Script to search for character strings in SQL Server databases – version 1

Sometimes I have to look for strings in data. Maybe I’m building a report or a query and I know the data from the application or capture screen but I don’t know the column. I have various ways of finding the column this data resides in but sometimes its called something completely different. So I cobbled together a script to search for this text, however, it could do with brushing up with newer transact-sql techniques like CTEs and ROW_NUMBERS but here’s the old script anyways, version 1 perchance…

When you run this it’s best to switch to Text Output, a quick CTRL-T will sort you out…

use [yourdbhere]

drop table #searchdata
select IDENTITY(int,1,1) as ID, ‘select [‘ + name + ‘] from ‘ + OBJECT_NAME(id) + ‘ WHERE [‘ + name + ‘] like ”%SQL Server 2014%”’ as SQLString into #searchdata from syscolumns where xtype in (167,175,231,239) and OBJECT_NAME(id) not like ‘sys%’ and LEFT(name,1) <> ‘@’
and OBJECT_NAME(id) not like ‘queue%’ and name not like ‘filestream%’

set nocount on
declare @minid int
declare @sqlstring nvarchar(4000)
set @minid = 1
while @minid is not null
begin
select @sqlstring = sqlstring from #mytest where ID = @minid
execute sp_executesql @sqlstring
if @@ROWCOUNT <> 0
print @sqlstring
select @minid = MIN(id) from #mytest where ID > @minid
end