Find Keyword in whole Database

T-SQL Script: Find Keyword in whole Database

Ref:

http://www.rad.pasfu.com/index.php?/archives/65-T-SQL-Script-Find-Keyword-in-whole-Database.html#extended

There are some times that you need to find all occurrence (or some of them) of a keyword in all columns/tables in a database, This is a usual scenario in data profiling.

I wrote script below to fulfill this requirement, this script will search for specific keyword in all columns and tables of a database (SQL Server database) and returns list of Schemas, Tables, Columns and occurrence of keyword as a result

declare @keyword nvarchar(max)

set @keyword='David'

declare @schema varchar(max)

declare @table varchar(max)

declare @column varchar(max)

declare @sqlstatement nvarchar(max)

declare @totalrecords int

declare @counter int

declare @occurrence int

declare @objects table

(

SchemaName varchar(max),

TableName varchar(max),

ColumnName varchar(max),

IsProcessed bit,

Occurrence int)

insert into @objects(SchemaName,TableName,ColumnName,IsProcessed)

select sch.name,tab.name,col.name,0

from sys.columns col

inner join sys.tables tab

on col.object_id=tab.object_id

inner join sys.schemas sch

on tab.schema_id=sch.schema_id

where col.system_type_id not in (

34,--image

241--xml

)

order by sch.name,tab.name,col.name

select @totalrecords=count(*) from @objects

set @counter=0

while (@counter<=@totalrecords)

begin

select top 1 @schema=SchemaName,@table=TableName,@column=ColumnName

from @objects

where isprocessed=0

order by SchemaName,TableName,ColumnName

set @sqlstatement='select @occurrence=count(*) from ['+@schema+'].['+@table+'] where ['+@column+'] like ''%'+@keyword+'%'''

exec sp_executesql @query=@sqlstatement,@params=N'@occurrence int output',@occurrence=@occurrence output

update @objects

set IsProcessed=1,

Occurrence=@occurrence

where SchemaName=@schema and TableName=@table and ColumnName=@column

set @counter=@counter+1

end

select SchemaName,TableName,ColumnName,Occurrence from @objects

This script took 5 minutes to run on a database with 120 tables (total number of 1400 columns)

This is sample result of script:

To run this script you just need to open a query window in SSMS, select database, change value assigned to @keyword variable and run the query.