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
wow, I did not know this script exists? I have made a very slow alternative with PHP but this looks very well! I will try it out and check if it is faster!
Reply to this
The author here has given us a detailed description of how to use the script that searches all fields in a database for a text string. I found this blog to be of immense help. I am just a beginner in SQL but I could easily make out the idea conveyed by the author. It is written in a simple manner. The programming part is well documented. I sincerely hope that he will continue the good work in future also.
Reply to this
Your website is excellent , i have been looking for this information everywhere, many many thanks
Reply to this
There is no way to perdict what maximum relevance rank could be. While working with full text searches one may want to show percentages as the criteria for indicating how close a particular record was to the search query. To achieve this, one way is to select the maximum relevance rank or score and then use it( as a denominator ) with every single record score to get percentage equivalent of score.
Reply to this
It is so astonishing that you have got so many people to assist you draw up the blog and it is genuinely agitating to read and love all the contingents of your chronicles that have never adverted me.
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
Your experience is great and learning for other people.
3D Scale Model Rendering, 3D Floor Plans, House Plans & Engineering Models
=================================================================
Remove Background, Cropping, Raster Graphics, Photo Editing Clipping Path, Vector Graphics Services
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!!!
http://wwwblackack4webcom/
Reply to this