search-sql-text.sql

-- try and do dirty reads, and turn off the record counting unless you want
-- spam in the middle of your prints below. you can also just do a select
-- instead of a print.
set transaction isolation level read uncommitted
set nocount on

-- vars to hold the commands to queue up and the command var for the one we'll run
-- identity is used to preserve order in case we decide to have a particular order
-- otherwise it will just run in the order we create the commands
declare @command nvarchar(max), @id int
declare @commands table(id int IDENTITY(1,1) PRIMARY KEY CLUSTERED, command nvarchar(max))

-- strint to look for
declare @StringToFind varchar(50)
select @StringToFind = 'some text you want to find'

-- just using a CTE here to make it a bit more readable, this could
-- simply be a select statement. this will build the commands to execute
with a (t, c) as (
    select
        '[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' [t]
        , '[' + column_name    + ']' [c]
    from
        INFORMATION_SCHEMA.COLUMNS
    where
        data_type in ('nvarchar','nchar','varchar','char')
        and character_maximum_length >= len(@StringToFind)
)

insert @commands(command)
select
  'if exists(select * from ' + t + ' with (nolock ) where ' + c + ' like ''%' + @StringToFind + '%'')'
  + ' print ''found ' + @StringToFind + ' in '+ t + ' in column ' + c + ''''
from
  a

-- if you just want to see the commands you can run the following
-- select * from @commands

-- if you don't want to run the commands, comment out the following.

-- run commands
while (select count(*) from @commands) != 0
begin
  select top 1 @id=id,@command=command from @commands order by id asc
  exec( @command )
  delete from @commands where id=@id
end