Technical Article

Search All Columns in all tables

,

This script will quickly generate DML to search all the tables in the current database for a given string. It could be modified to execute the generated code if desired. If you are running this in a <2005 environment, convert the VARCHAR(MAX)s to VARCHAR(8000) and be careful of overflowing the variable if you have very wide tables. To use this script, simply set the value of @SearchItem to whatever string it is you are searching for on line 2 then run in the database you wish to search.

DECLARE @searchSQL AS VARCHAR(MAX)
DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%YOURSTRING%'

SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' + 
            CAST(Ordinal_position AS VARCHAR(MAX)) + 
            ' THEN '' OR  '' + IC.Column_Name + '' LIKE  ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + 
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' + 
            CAST(Ordinal_position AS VARCHAR(MAX)) + 
            ' THEN IC.Column_Name + '' LIKE  ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns 
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position

EXEC('SELECT REPLACE(Query, ''WHERE  OR'', ''WHERE'') FROM 
(SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as tbl, * FROM ['' +  
CAST(TABLE_SCHEMA AS VARCHAR(MAX)) + ''].'' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '' WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'',  ''varchar'', ''nchar'', ''nvarchar'')
GROUP BY TABLE_NAME, TABLE_SCHEMA) S')

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating