Ever had the need to find out where data is stored in your MS SQL Database?
I sometimes do and I find it often faster to simply search every table and every column within these table than to try and hunt down the schema definitions. I get a coffee while the script crunches away. The @SearchStrColumnName option is there mostly for when you search integers, as you may get too many false positives.
Edit: GitHub Gist works awesome, will have to do that for the previous posts too! Just paste the URL, and wordpress will do the rest, in this case display a fully highlighted T-SQL code.
Edit 4/8/2013 – @SearchStrColumnName wasn’t properly escaped before and this parameter didn’t really work.
Edit 4/9/2013 – Added parameter @FullRowResult, this will cause it to return the full row for each hit. This is usefull when you need to lookup or find related info to the search term.
Edit 4/12/2013 – Added parameter @SearchStrTableName, to limit in what tables we are going to search.
Edit 5/2/2013 – Now also searching in type uniqueidentifier (GUID)
Edit 5/22/2013 – Additional numeric data types will be searched.
Edit 1/7/2014 – Can now also search timestamp value (known as rowversion)
Edit 4/18/2014 – Added a Top parameter that works together with @FullRowResult. This is to help limit the return if a search string is found in a table too often
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* Reto Egeter, fullparam.wordpress.com */ | |
| DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int | |
| SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */ | |
| SET @FullRowResult = 1 | |
| SET @FullRowResultRows = 3 | |
| SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */ | |
| SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */ | |
| SET @SearchStrInXML = 0 /* Searching XML data may be slow */ | |
| IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results | |
| CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20)) | |
| SET NOCOUNT ON | |
| DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110) | |
| SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''') | |
| DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20)) | |
| WHILE @TableName IS NOT NULL | |
| BEGIN | |
| SET @TableName = | |
| ( | |
| SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) | |
| FROM INFORMATION_SCHEMA.TABLES | |
| WHERE TABLE_TYPE = 'BASE TABLE' | |
| AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME) | |
| AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName | |
| AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 | |
| ) | |
| IF @TableName IS NOT NULL | |
| BEGIN | |
| DECLARE @sql VARCHAR(MAX) | |
| SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE | |
| FROM INFORMATION_SCHEMA.COLUMNS | |
| WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2) | |
| AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1) | |
| AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ') | |
| AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)' | |
| INSERT INTO @ColumnNameTable | |
| EXEC (@sql) | |
| WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable) | |
| BEGIN | |
| PRINT @ColumnName | |
| SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable | |
| SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' | |
| WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),''' | |
| ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' | |
| FROM ' + @TableName + ' (NOLOCK) ' + | |
| ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' | |
| WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' | |
| ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue | |
| INSERT INTO #Results | |
| EXEC(@sql) | |
| IF @@ROWCOUNT > 0 IF @FullRowResult = 1 | |
| BEGIN | |
| SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' + | |
| ' FROM ' + @TableName + ' (NOLOCK) ' + | |
| ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' | |
| WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' | |
| ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue | |
| EXEC(@sql) | |
| END | |
| DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName | |
| END | |
| END | |
| END | |
| SET NOCOUNT OFF | |
| SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results | |
| GROUP BY TableName, ColumnName, ColumnValue, ColumnType |
fullparam 
Just a small hint… if you want to search all Views instead, this is easily possible with this.
Just change:
TABLE_TYPE = ‘BASE TABLE’
into
TABLE_TYPE = ‘VIEW’
And BAMMMM!
My good sr..
For sure you gain your spot in heaven for that!
Tks!
Excellent, this saves me a ton of time. Thank you for posting!
Thank you! very useful :)
I had to add “,@FullRowResultRows Bit” behind the @Declare to make it work, beyond that it works like advertised
Absolutely right Rick. I am unsure how I missed that. Must have been a copy paste issue :)
Edit: It’s now getting fixed.
hello is it possible to searh a null value ?
AWESOME mate, saved me hours of work. Hats off.
Msg 207, Level 16, State 1, Line 71
Invalid column name ‘TableName’.
Msg 207, Level 16, State 1, Line 71
Invalid column name ‘ColumnType’.
Msg 207, Level 16, State 1, Line 70
Invalid column name ‘TableName’.
Msg 207, Level 16, State 1, Line 70
Invalid column name ‘ColumnType’.
Sorry possibly a dumb question, but what am I doing wrong?? I get the above.
It looks like for some reason you dropped the table #Results?
Whenever it finds something it put it into the #Results table, and at the end basically does a SELECT from it.
If you are using the parameter
SET @FullRowResult = 1
you can also decide to not even use this temp table since FullRowResult means we echo each found row (limited by @FullRowResultRows)
This is genius. I have been searching and searching, and nothing has worked properly. Your solution is exactly what I need. Thank you so incredibly much!
I just wanted to drop a huge THANK YOU here for this excellent script. You have saved my day searching out a needle in a giant haystack. Great work, thanks for sharing!
It would like usefull add a parameter to limit the search for :
– a group of table
– like %table%
– table in (select mytablename from myFAVtables)
what do you think ?
Learned a few things from using your query so, for that, thanks!
Quick question, however; Would it not be more efficient to do the COUNT (*) and GROUP BY statement before inserting, possibly, hundreds of thousands of rows into #Results?
For my use case, the initial run with your unaltered code took 55 minutes and it returned everything I needed. However, after adding the COUNT and GROUP BY in the SET @sql statement, before inserting into #Results, it took 30 minutes to run.
Just my 2 cents. Thanks again for the query!
Thanks, unfortunately I haven’t used MS SQL server in two years. Can you post your updated solution? It sounds like a nice improvement.
Sure thing!
My changes:
–Update the temp table to include a column for COUNT (Line 12)
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20),[COUNT] nvarchar(20))
–Update the @sql statement to COUNT(*) and GROUP BY (Line 46-54)
SET @sql = ‘SELECT ”’ + @TableName + ”’,”’ + @ColumnName + ”’,’ + CASE @ColumnType WHEN ‘xml’ THEN ‘LEFT(CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX)), 4096),”’
WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘),”’
ELSE ‘LEFT(‘ + @ColumnName + ‘, 4096),”’ END + @ColumnType + ”’,COUNT(*)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue + ‘GROUP BY’ + @TableName + ‘.’ + @ColumnName
INSERT INTO #Results
EXEC(@sql)
–Update the SELECT statement at the end of the query (Line 70 & 71)
SELECT * FROM #Results
ORDER BY TableName;
DROP TABLE #Results;
Let me know if you have any questions.
Here’s the full query I use, with a few tweaks (naming, semi colons, capitalization) here and there:
DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = ‘ 101’; — Use LIKE syntax
SET @FullRowResult = 0;
SET @FullRowResultRows = 3;
SET @SearchStrTableName = NULL; — NULL for all tables, uses LIKE syntax
SET @SearchStrColumnName = NULL; — NULL for all columns, uses LIKE syntax
–SET @SearchStrInXML = 0; — Searching XML data may be slow
IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL
BEGIN
DROP TABLE #Results
END;
CREATE TABLE #Results (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128), COLUMN_VALUE nvarchar(max),COLUMN_TYPE nvarchar(20),[COUNT] nvarchar(20), INSPECT_QUERY nvarchar(max))
SET NOCOUNT ON;
DECLARE @TableName nvarchar(256) = ”,@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,””);
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
WHILE @TableName IS NOT NULL
BEGIN
SET @TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND TABLE_CATALOG = ‘JDE_DEVELOPMENT’
AND TABLE_SCHEMA = ‘TESTDTA’
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)), ‘IsMSShipped’) = 0
);
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = ‘SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(”’ + @TableName + ”’, 2)
AND TABLE_NAME = PARSENAME(”’ + @TableName + ”’, 1)
AND DATA_TYPE = ”nchar”
AND CHARACTER_MAXIMUM_LENGTH = 12
–AND DATA_TYPE IN (‘ + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,’%’,”),’_’,”),'[‘,”),’]’,”),’-‘,”)) = 1 THEN ”’tinyint”,”int”,”smallint”,”bigint”,”numeric”,”decimal”,”smallmoney”,”money”,’ ELSE ” END + ”’char”,”varchar”,”nchar”,”nvarchar”,”timestamp”,”uniqueidentifier”’ + CASE @SearchStrInXML WHEN 1 THEN ‘,”xml”’ ELSE ” END + ‘)
AND COLUMN_NAME LIKE COALESCE(‘ + CASE WHEN @SearchStrColumnName IS NULL THEN ‘NULL’ ELSE ”” + @SearchStrColumnName + ”” END + ‘,COLUMN_NAME)’
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = ‘SELECT ”’ + @TableName + ”’,”’ + @ColumnName + ”’,’ + CASE @ColumnType WHEN ‘xml’ THEN ‘LEFT(CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX)), 4096),”’
WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘),”’
ELSE ‘LEFT(‘ + @ColumnName + ‘, 4096),”’ END + @ColumnType + ”’,COUNT(*),
”SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ”’ + @QuotedSearchStrColumnValue +”’;”
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue + ‘GROUP BY’ + @TableName + ‘.’ + @ColumnName
INSERT INTO #Results
EXEC(@sql)
IF (@@ROWCOUNT > 0 AND @FullRowResult = 1)
BEGIN
SET @sql = ‘SELECT TOP ‘ + CAST(@FullRowResultRows AS VARCHAR(3)) + ‘ ”’ + @TableName + ”’ AS [TableFound],”’ + @ColumnName + ”’ AS [ColumnFound],”FullRow>” AS [FullRow>],*’ +
‘ FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue
EXEC(@sql)
END;
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END;
END;
END;
SET NOCOUNT OFF;
SELECT * FROM #Results
ORDER BY TABLE_NAME;
DROP TABLE #Results;
Ooops… Ignore these 2 lines
AND TABLE_CATALOG = ‘JDE_DEVELOPMENT’
AND TABLE_SCHEMA = ‘TESTDTA’
AWESOME!!!!!!!!!!!!!!!!!
How can i search a numeric (123666.664 for example)? when i try it no success.
Could you please share some simple select query for searching the string in database tables
Can someone take this query and convert it to where the results appear as one result? Instead of this thing looping?
How would you specify a single table to search?
Someone tried this in a production database that’s 25TB where I work. It wrecked performance, over 1 trillion reads. SAN slowed to a crawl. Please consider not doing this in a large prod environment.
I’m hoping you are still active on this, I came across this trying to track down some NULLs in our PG SQL database. I have the same question as Ahmet, is there any way to get this to return NULLs?
Hi CJ,
Searching for NULL would be a different query I think. For most relational databases having some NULL values is completely normal tho, so I think any query will likely return a lot of results!