T-SQL Script: Find Keyword in whole Database

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 (




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

select @totalrecords=count(*) from @objects

set @counter=0

while (@counter<=@totalrecords)


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,


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

set @counter=@counter+1


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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply