-- 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
Obviously you can adjust this to taste for different fields, types, outputs, etc. Originally I simply used a select statement and just copy/pasted the gen'd code, but since I was asked for something that would 'do' this, I figured I would go ahead and queue up and exec the commands. I also switched to a CTE just to provide a little separation between 'get the list of tables/columns' and 'produce the list of commands' in case that was needed. The set trans up at the top should remove the need for the with nolocks, but I figured just in case someone snipped that code, they would think twice before issuing broader locking selects in a scope like this so I dropped some nolock hints in. Of course, if you *must* have clean reads, you would have to remove that. This should, according to docs issue and honor no locks during its life but as always, beware when running something like this in prod.
Enjoy!