Searching All Fields in a Database for a Text String

The other day a person in my QA department came to me with a request for a script that searches all fields in a database for a text string.  They were unsure where a configuration value was being stored on the system.  While you could use SQL Profiler to find this information, I thought writing the script would be fun. 

Below is the script:

DECLARE @tblTables TABLE (Col varchar(128), Tab varchar(128), Sch varchar(128))
DECLARE @Tab varchar(128), @Col varchar(128), @Sch varchar(128), @SearchValue varchar(MAX), @Query varchar(MAX)

SET @SearchValue = 'Smith' --set this to the string you wish to find

INSERT INTO @tblTables (Col, Tab, Sch)
SELECT c.Name AS Col, t.Name AS Tab, s.[Name] AS Sch
FROM sys.columns c
    JOIN sys.tables t ON t.[object_id] = c.[object_id]
    JOIN sys.types ty ON ty.[user_type_id] = c.[user_type_id]
    JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
WHERE t.[type] = 'U'
    AND ty.[name] IN ('varchar','char','nvarchar','nchar')

-- use temp table for persistence with executing dynamic sql
CREATE TABLE #tblValues (Col varchar(128), Tab varchar(128), Sch varchar(128), ColValue varchar(MAX))

WHILE EXISTS (SELECT 1 FROM @tblTables)
BEGIN
    SELECT TOP(1) @Col = Col, @Tab = Tab, @Sch = Sch
    FROM @tblTables
   
    SET @Query =    'INSERT INTO #tblValues (Tab, Col, Sch, ColValue)' +
                    ' SELECT ''' + @Tab + ''' AS Tab, ''' + @Col + ''' AS Col, ''' + @Sch + ''' AS Sch, [' + @Col + '] AS ColValue' +
                    ' FROM [' + @Sch + '].[' + @Tab + ']' +
                    ' WHERE [' + @Col + '] LIKE ''%' + @SearchValue + '%'''

   
    EXEC (@Query)
       
    DELETE TOP(1) FROM @tblTables
END

SELECT * FROM #tblValues

DROP TABLE #tblValues


 

What did you think of this article?




Trackbacks
  • No trackbacks exist for this entry.
Comments
Page: 1 of 1
Page: 1 of 1
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

 Website

Your comment is 0 characters limited to 3000 characters.