Pesquisar por uma palavra em todas as tabelas e campos

 
declare @idtabela int, @tabela varchar(255), @coluna varchar(255), @valorProcurado varchar(255)
 
--Coloque aqui a palavra ou expressão que deseja procurar
set @valorProcurado = 'comercio'
 
drop table ##tmpFindString
 
create table ##tmpFindString (table_name varchar(255), string varchar(255))
 
DECLARE db_cursor CURSOR FOR  
select id from sys.sysobjects where xtype = 'u'
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @idtabela
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
 
DECLARE db_cursorColunas CURSOR FOR  
select a.name as tabela, b.name as coluna from sys.sysobjects a
inner join
sys.syscolumns b
on a.id = b.id 
where b.xtype in (167,175,231,239) and a.xtype = 'u'
and a.id = @idtabela
/*
167 varchar
175 char
231 nvarchar
239 nchar*/
OPEN db_cursorColunas   
FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
exec('
insert ##tmpFindString
select '''  + @tabela + ''', string = '''+@valorProcurado+'''
from ' + @tabela + ' where '+@coluna+' like ''%'+@valorProcurado+'%''')
  
  FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna 
END   
 
CLOSE db_cursorColunas   
DEALLOCATE db_cursorColunas 
  
  FETCH NEXT FROM db_cursor INTO @idtabela 
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor 
 
  
select distinct * from ##tmpFindString