Finding a particular string in the database

Sometimes, you may need to find a value in the string columns (char, varchar, etc) in all

the tables in a database. This can be for many reasons for instance, say i need to change that value to something else or may it be to just check the existence of the string in the database. Most of the time we end up doing is a manual search by querying each table, the lay man search!

Here is a simple query which does it for you:

DECLARE @sql VARCHAR(MAX),@search VARCHAR(100),@tableSchema VARCHAR(100)

SET @sql=''

SET @search='Alpha' -- 'your search string'
SET @tableSchema='DW'

SELECT

@sql=@sql+

'SELECT DISTINCT ''['+c.table_name+']'' AS TABLE_NAME,'''

+c.column_name+''' AS COLUMN_NAME,['+c.column_name+'] AS COLUMN_VALUE FROM ['+c.TABLE_SCHEMA+'].['+

+c.table_name+'] WHERE ['+c.column_name+'] LIKE N''%'+@search+'%'' UNION ALL '

FROM

INFORMATION_SCHEMA.COLUMNS AS C INNER JOIN INFORMATION_SCHEMA.TABLES AS T ON C.TABLE_NAME=T.TABLE_NAME

WHERE

T.TABLE_TYPE='BASE TABLE' AND DATA_TYPE LIKE '%CHAR%' AND T.TABLE_SCHEMA = @tableSchema

SET @sql=left(@sql,len(@sql)-10)

EXEC(@SQL)



//after where column name: '] = N'''+@search+''' UNION ALL '