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
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
I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post
Reply to this
Your site is perfectly. Great work, I liked. Always, there is many interesting.
Reply to this
On my website I don't require visitors to use an email address when they comment. This means that all users who leave a comment without an email address are being grouped together by SEO Super Comments which is wrong.
Reply to this
thanks for giving the ideas of how to share all fields in the data base for a Text String,
thanks and regards to all.
Reply to this
Thanks for great post........
Reply to this
Very interesting and useful information! I am not a frequent visitor of blogs, but sometimes if I find something valuable I can leave the site without saying that your blog is a real treasure and your work is worth good words!!!
Reply to this
I will read time to time that
Reply to this