T-SQL Script: Find Keyword in whole Database

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
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 eight 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *