Search String in DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Search String in DB

Author

Topic

chuchi
Starting Member

2 Posts

Posted - 2013-04-25 : 11:35:22

Hi there

I hope you can help me
I'm looking for a query that lets me find a specific text in a lets say "unknown" Database.
By this a mean: I wanna search every column in every table if a "text" exists.

I couldnt find anything (maybe i searched for the wrong turns), but i created a query on my own:

CREATE PROCEDURE [dbo].[x_FeldinhaltSuchen]
@i_SucheNach varchar(255)
AS
DECLARE @ObjID int
DECLARE @Table varchar(50)
DECLARE @Column varchar(50)
DECLARE @PrimaryIDColumn varchar(50)
DECLARE @Search varchar(255)
DECLARE @cmd varchar(1024)

-- Temporäre Tabelle für die Rückgabe
CREATE Table #temp
(
[Table] varchar(50),
[Column] varchar(50)
)
-- Suchwert definieren (mit % für Wildcard-Suche)
SELECT @Search = '%' + @i_SucheNach + '%'

DECLARE FieldCursor CURSOR FOR (SELECT sys.tables.object_id, sys.tables.name, sys.columns.name FROM sys.columns
join sys.tables ON sys.tables.object_id = sys.columns.object_id
WHERE system_type_id IN (SELECT system_type_id FROM sys.types WHERE name in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar')))
OPEN FieldCursor
FETCH NEXT FROM FieldCursor
INTO @ObjID, @Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @PrimaryIDColumn = ''
SELECT @PrimaryIDColumn = isnull(name, '') FROM sys.columns WHERE object_id = @ObjID AND is_identity = 1
-- Überprüfung des Inhalts, sofern Inhalt existiert, wird er in die temporäre Tabelle geschrieben
-- Sofern PrimaryID existiert, mit PrimaryID auf Existenz prüfen -> Abfragen sind schneller
IF @PrimaryIDColumn <> ''
SELECT @cmd = 'IF EXISTS(SELECT [' + @PrimaryIDColumn
ELSE
SELECT @cmd = 'IF EXISTS(SELECT [' + @Column
SELECT @cmd = @cmd + '] FROM [' + @Table + '] WHERE convert(varchar(50), [' + @Column + ']) LIKE ''' + @Search + ''') '
SELECT @cmd = @cmd + 'INSERT INTO #temp SELECT ''' + @Table + ''', ''' + @Column + ''''
-- Ausführen des Commands: () da der Command in einem String ist
print @cmd
EXEC (@cmd)
SELECT @ObjID = 0
SELECT @Table = '', @Column = ''
-- nächstes Objekt
FETCH NEXT FROM FieldCursor
INTO @ObjID, @Table, @Column
END
CLOSE FieldCursor
DEALLOCATE FieldCursor
-- Ausgabe der Tabelle
SELECT * FROM #temp
DELETE #temp
DROP TABLE #temp
Finish:
RETURN 0
GO

So what it does is pretty obvious
For every column it creates an "if exist/insert"-String which is then executed
The problem is: its very slow

Is there a proper and faster way to search?
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 11:46:15

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chuchi
Starting Member

2 Posts

Posted - 2013-05-06 : 10:05:06

Thank you

that did the job!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 00:22:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources