Comando SQL Pesquisa uma string em qualquer banco

/*

By Ulisses Ramos

Objetivo: Pesquisar uma string em qualquer banco sqlserver

Data criação: 20 de janeiro de 20111

  */

use SEUBANCO

GO

IF OBJECT_ID('tempdb.dbo.#TabelasSistema') IS NOT NULL

    DROP TABLE #TabelasSistema

GO

DECLARE @StrPesquisa varchar(50)

-- TROQUE AQUI PELA STRING QUE QUER PESQUISAR

set @StrPesquisa = '''%'+'Ulisses'+'%'''

SELECT  +'select '+CAST (row_number() over (order by  b.name  asc) AS VARCHAR(20))+

          ',* from '+ b.name +' where '+ a.name +' LIKE '+@StrPesquisa AS COMANDOSQL

Into #TabelasSistema

   

    FROM sys.columns a

    join sys.tables b

    on (a.object_id = b.object_id)

    where a.system_type_id in (

    select system_type_id from

    sys.types aa where aa.collation_name is not null)

    ORDER BY  b.name

       

    GO

Declare @SQL varchar(512)

While (Select Count(*) From #TabelasSistema) > 0

Begin

    Set @SQL      = (Select TOP 1 COMANDOSQL From #TabelasSistema)

    Exec (@SQL)

    Delete From #TabelasSistema Where COMANDOSQL= @SQL

   

End

GO

GO

IF OBJECT_ID('tempdb.dbo.#TabelasSistema') IS NOT NULL

    DROP TABLE #TabelasSistema

GO

DECLARE @StrPesquisa varchar(50)

set @StrPesquisa = '''%'+'rodrigo'+'%'''

SELECT  +'select '+CAST (row_number() over (order by  b.name  asc) AS VARCHAR(20))+

          ',* from '+ b.name +' where '+ a.name +' LIKE '+@StrPesquisa AS COMANDOSQL

Into #TabelasSistema

   

    FROM sys.columns a

    join sys.tables b

    on (a.object_id = b.object_id)

    where a.system_type_id in (

    select system_type_id from

    sys.types aa where aa.collation_name is not null)

    ORDER BY  b.name

       

GO