Realizando uma busca textual em todas as colunas, de todas as tabelas no SQL Server

Post Views 14,825 views

Reading time 3 minutes

Olá, queridos leitores.
Bom dia!

Hoje eu quero falar sobre uma necessidade no SQL Server onde eu precisei procurar em quais colunas, de quais tabelas, um registro foi gravado. Para resolver esse problema, eu criei uma Stored Procedure que realiza essa busca:

stpBusca_String_Tabela:

USE [dirceuresende]
GO

CREATE PROCEDURE [dbo].[stpBusca_String_Tabela](
    @Ds_Texto VARCHAR(100), 
    @Ds_Banco AS VARCHAR(100), 
    @Ds_Filtro_Tabela AS VARCHAR(100) = NULL,
    @Ds_Filtro_Coluna AS VARCHAR(100) = NULL,
    @Ds_Tabela_Destino AS VARCHAR(100) = NULL
)
AS BEGIN
    
    SET NOCOUNT ON
    
    DECLARE @query VARCHAR(MAX)
    SET @query = '
    
    USE ' + @Ds_Banco + '
    
    IF (OBJECT_ID(''tempdb..##lista_colunas'') IS NOT NULL) DROP TABLE ##lista_colunas

    SELECT
        tabelas.TABLE_SCHEMA					AS [Schema],
        tabelas.TABLE_NAME						AS Tabela,
        colunas.COLUMN_NAME						AS Coluna,
        colunas.DATA_TYPE						AS Tipo,
        colunas.NUMERIC_PRECISION_RADIX			AS Tamanho
    INTO
        ##lista_colunas
    FROM 
        INFORMATION_SCHEMA.TABLES tabelas
        JOIN INFORMATION_SCHEMA.COLUMNS colunas ON (tabelas.TABLE_NAME = colunas.TABLE_NAME AND tabelas.TABLE_SCHEMA = colunas.TABLE_SCHEMA)
    WHERE 
        colunas.DATA_TYPE IN(''text'', ''ntext'', ''varchar'', ''nvarchar'')
        AND tabelas.TABLE_TYPE = ''BASE TABLE''
    ORDER BY
        1, 2, 3'
        
    EXEC(@query)
    
    
    IF (@Ds_Filtro_Tabela IS NOT NULL)
    BEGIN 
    
        DELETE FROM ##lista_colunas WHERE Tabela NOT LIKE '%' + @Ds_Filtro_Tabela + '%'
        
    END
    
    
    
    IF (@Ds_Filtro_Coluna IS NOT NULL)
    BEGIN 
    
        DELETE FROM ##lista_colunas WHERE Coluna NOT LIKE '%' + @Ds_Filtro_Coluna + '%'
        
    END
    


    ALTER TABLE ##lista_colunas ADD Id INT IDENTITY(1,1)
    
    
    DECLARE 
        @numeroColunas INT = 0,
        @contadorColunas INT = 1,
        @numeroLinhas INT = 0,
        @contadorLinhas INT = 1,
        @schema VARCHAR(100),
        @tabela VARCHAR(100),
        @coluna VARCHAR(100)
        
        
    SET @numeroColunas = (SELECT COUNT(*) FROM ##lista_colunas)
    
    
    -- Tabela que guardará o resultado final
    IF (OBJECT_ID('tempdb..##Resultado_Final') IS NOT NULL) DROP TABLE ##Resultado_Final
    
    CREATE TABLE ##Resultado_Final (
        ID INT IDENTITY(1,1),
        [Schema] varchar(100),
        Tabela VARCHAR(100),
        Coluna VARCHAR(100),
        Resultado VARCHAR(MAX)
    )
    
    
    IF (OBJECT_ID('tempdb..##Resultado_Busca') IS NOT NULL) DROP TABLE ##Resultado_Busca
    
    CREATE TABLE ##Resultado_Busca (
        ID INT IDENTITY(1,1),
        Texto_Encontrado VARCHAR(MAX)
    )
    
    
    WHILE (@contadorColunas <= @numeroColunas)
    BEGIN	
    
        SELECT @schema = [Schema], @tabela = [Tabela], @coluna = [Coluna] FROM ##lista_colunas WHERE Id = @contadorColunas
        
        SET @query = 'TRUNCATE TABLE ##Resultado_Busca; INSERT INTO ##Resultado_Busca(Texto_Encontrado) SELECT [' + @coluna + '] FROM [' + @Ds_Banco + '].[' + @schema + '].[' + @tabela + '] WHERE [' + @coluna + '] LIKE ''%' + @Ds_Texto + '%'''
        EXEC(@query)
        
        SET @contadorLinhas = 1
        SET @numeroLinhas = (SELECT COUNT(*) FROM ##Resultado_Busca)
        
        WHILE(@contadorLinhas <= @numeroLinhas)
        BEGIN
    
            SET @query = (SELECT Texto_Encontrado FROM ##Resultado_Busca WHERE Id = @contadorLinhas)
            
            IF(@query IS NOT NULL)
            BEGIN
    
                INSERT INTO ##Resultado_Final([Schema], Tabela, Coluna, Resultado)
                SELECT @schema, @tabela, @coluna, @query
                
            END
            
            SET @contadorLinhas = @contadorLinhas + 1
            
        END
        
        SET @contadorColunas = @contadorColunas + 1
    
    END
    
    
    
    IF (@Ds_Tabela_Destino IS NOT NULL)
    BEGIN
    
        SET @query = 'SELECT * INTO ' + @Ds_Tabela_Destino + ' FROM ##Resultado_Final'
        EXEC(@query)
    
    END
    ELSE BEGIN
    
        SELECT [Schema], Tabela, Coluna, Resultado FROM ##Resultado_Final
    
    END
    
    
    -- Apaga as tabelas usadas pela SP
    IF (OBJECT_ID('tempdb..##lista_colunas') IS NOT NULL) DROP TABLE ##lista_colunas
    IF (OBJECT_ID('tempdb..##Resultado_Busca') IS NOT NULL) DROP TABLE ##Resultado_Busca
    IF (OBJECT_ID('tempdb..##Resultado_Final') IS NOT NULL) DROP TABLE ##Resultado_Final
    

END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

USE [dirceuresende]

CREATE PROCEDURE [dbo].[stpBusca_String_Tabela](

@Ds_Texto VARCHAR(100),

@Ds_Banco AS VARCHAR(100),

@Ds_Filtro_Tabela AS VARCHAR(100) = NULL,

@Ds_Filtro_Coluna AS VARCHAR(100) = NULL,

@Ds_Tabela_Destino AS VARCHAR(100) = NULL

)

AS BEGIN

SET NOCOUNT ON

DECLARE @query VARCHAR(MAX)

SET @query = '

USE ' + @Ds_Banco + '

IF (OBJECT_ID(''tempdb..##lista_colunas'') IS NOT NULL) DROP TABLE ##lista_colunas

SELECT

tabelas.TABLE_SCHEMA AS [Schema],

tabelas.TABLE_NAME AS Tabela,

colunas.COLUMN_NAME AS Coluna,

colunas.DATA_TYPE AS Tipo,

colunas.NUMERIC_PRECISION_RADIX AS Tamanho

INTO

##lista_colunas

FROM

INFORMATION_SCHEMA.TABLES tabelas

JOIN INFORMATION_SCHEMA.COLUMNS colunas ON (tabelas.TABLE_NAME = colunas.TABLE_NAME AND tabelas.TABLE_SCHEMA = colunas.TABLE_SCHEMA)

WHERE

colunas.DATA_TYPE IN(''text'', ''ntext'', ''varchar'', ''nvarchar'')

AND tabelas.TABLE_TYPE = ''BASE TABLE''

ORDER BY

1, 2, 3'

EXEC(@query)

IF (@Ds_Filtro_Tabela IS NOT NULL)

BEGIN

DELETE FROM ##lista_colunas WHERE Tabela NOT LIKE '%' + @Ds_Filtro_Tabela + '%'

END

IF (@Ds_Filtro_Coluna IS NOT NULL)

BEGIN

DELETE FROM ##lista_colunas WHERE Coluna NOT LIKE '%' + @Ds_Filtro_Coluna + '%'

END

ALTER TABLE ##lista_colunas ADD Id INT IDENTITY(1,1)

DECLARE

@numeroColunas INT = 0,

@contadorColunas INT = 1,

@numeroLinhas INT = 0,

@contadorLinhas INT = 1,

@schema VARCHAR(100),

@tabela VARCHAR(100),

@coluna VARCHAR(100)

SET @numeroColunas = (SELECT COUNT(*) FROM ##lista_colunas)

-- Tabela que guardará o resultado final

IF (OBJECT_ID('tempdb..##Resultado_Final') IS NOT NULL) DROP TABLE ##Resultado_Final

CREATE TABLE ##Resultado_Final (

ID INT IDENTITY(1,1),

[Schema] varchar(100),

Tabela VARCHAR(100),

Coluna VARCHAR(100),

Resultado VARCHAR(MAX)

)

IF (OBJECT_ID('tempdb..##Resultado_Busca') IS NOT NULL) DROP TABLE ##Resultado_Busca

CREATE TABLE ##Resultado_Busca (

ID INT IDENTITY(1,1),

Texto_Encontrado VARCHAR(MAX)

)

WHILE (@contadorColunas <= @numeroColunas)

BEGIN

SELECT @schema = [Schema], @tabela = [Tabela], @coluna = [Coluna] FROM ##lista_colunas WHERE Id = @contadorColunas

SET @query = 'TRUNCATE TABLE ##Resultado_Busca; INSERT INTO ##Resultado_Busca(Texto_Encontrado) SELECT [' + @coluna + '] FROM [' + @Ds_Banco + '].[' + @schema + '].[' + @tabela + '] WHERE [' + @coluna + '] LIKE ''%' + @Ds_Texto + '%'''

EXEC(@query)

SET @contadorLinhas = 1

SET @numeroLinhas = (SELECT COUNT(*) FROM ##Resultado_Busca)

WHILE(@contadorLinhas <= @numeroLinhas)

BEGIN

SET @query = (SELECT Texto_Encontrado FROM ##Resultado_Busca WHERE Id = @contadorLinhas)

IF(@query IS NOT NULL)

BEGIN

INSERT INTO ##Resultado_Final([Schema], Tabela, Coluna, Resultado)

SELECT @schema, @tabela, @coluna, @query

END

SET @contadorLinhas = @contadorLinhas + 1

END

SET @contadorColunas = @contadorColunas + 1

END

IF (@Ds_Tabela_Destino IS NOT NULL)

BEGIN

SET @query = 'SELECT * INTO ' + @Ds_Tabela_Destino + ' FROM ##Resultado_Final'

EXEC(@query)

END

ELSE BEGIN

SELECT [Schema], Tabela, Coluna, Resultado FROM ##Resultado_Final

END

-- Apaga as tabelas usadas pela SP

IF (OBJECT_ID('tempdb..##lista_colunas') IS NOT NULL) DROP TABLE ##lista_colunas

IF (OBJECT_ID('tempdb..##Resultado_Busca') IS NOT NULL) DROP TABLE ##Resultado_Busca

IF (OBJECT_ID('tempdb..##Resultado_Final') IS NOT NULL) DROP TABLE ##Resultado_Final

END

Exemplos de utilização:

-- Realiza uma busca pela palavra "Dirceu" em todas as colunas e tabelas do database Clientes
EXEC dbo.stpBusca_String_Tabela
    @Ds_Texto = 'Dirceu' , -- varchar(100)
    @Ds_Banco = 'Clientes' -- varchar(max)

-- Realiza uma busca pela palavra "Dirceu" em todas as tabelas que contenham a string "Clientes" no database Clientes
EXEC dbo.stpBusca_String_Tabela
    @Ds_Texto = 'Dirceu' , -- varchar(100)
    @Ds_Banco = 'Clientes', -- varchar(max)
    @Ds_Filtro_Tabela = 'Clientes' -- varchar(max)

-- Realiza uma busca pela palavra "Dirceu" nas colunas que contenham a string "Cd_" das tabelas que contenham a string "Clientes" no database Clientes
EXEC dbo.stpBusca_String_Tabela
    @Ds_Texto = 'Dirceu' , -- varchar(100)
    @Ds_Banco = 'Clientes', -- varchar(max)
    @Ds_Filtro_Tabela = 'Clientes' -- varchar(max),
    @Ds_Filtro_Coluna = 'Cd_'

-- Realiza uma busca pela palavra "Dirceu" no database Clientes e grava o resultado na tabela temporária global ##Resultado
EXEC dbo.stpBusca_String_Tabela
    @Ds_Texto = 'Dirceu' , -- varchar(100)
    @Ds_Banco = 'Clientes' -- varchar(max),
    @Ds_Tabela_Destino = '##Resultado'

-- Realiza uma busca pela palavra "Dirceu" em todas as colunas e tabelas do database Clientes

EXEC dbo.stpBusca_String_Tabela

@Ds_Texto = 'Dirceu' , -- varchar(100)

@Ds_Banco = 'Clientes' -- varchar(max)

-- Realiza uma busca pela palavra "Dirceu" em todas as tabelas que contenham a string "Clientes" no database Clientes

EXEC dbo.stpBusca_String_Tabela

@Ds_Texto = 'Dirceu' , -- varchar(100)

@Ds_Banco = 'Clientes', -- varchar(max)

@Ds_Filtro_Tabela = 'Clientes' -- varchar(max)

-- Realiza uma busca pela palavra "Dirceu" nas colunas que contenham a string "Cd_" das tabelas que contenham a string "Clientes" no database Clientes

EXEC dbo.stpBusca_String_Tabela

@Ds_Texto = 'Dirceu' , -- varchar(100)

@Ds_Banco = 'Clientes', -- varchar(max)

@Ds_Filtro_Tabela = 'Clientes' -- varchar(max),

@Ds_Filtro_Coluna = 'Cd_'

-- Realiza uma busca pela palavra "Dirceu" no database Clientes e grava o resultado na tabela temporária global ##Resultado

EXEC dbo.stpBusca_String_Tabela

@Ds_Texto = 'Dirceu' , -- varchar(100)

@Ds_Banco = 'Clientes' -- varchar(max),

@Ds_Tabela_Destino = '##Resultado'

Francis disse:

September 4, 2023 às 11:48

Bom dia, como seria o código dessa procedure para o Oracle?

Responder
Renan disse:

July 5, 2018 às 11:48

Muito bom. Funcionou perfeitamente.

Responder
Valmir Pinheiro disse:

February 16, 2017 às 14:30

Rodei o Script e deu o seguinte erro:

“Msg 2714, Level 16, State 3, Procedure stpBusca_String_Tabela, Line 146
There is already an object named ‘stpBusca_String_Tabela’ in the database.”

Responder
- Dirceu Resende disse:
  
  February 16, 2017 às 18:18
  
  Valmir, esse erro aconteceu porque você deve ter executado o comando de create duas vezes e o banco avisou que esse objeto já existe 🙂
  
  Responder
Romildo Pereira disse:

November 28, 2016 às 08:26

Ao tentar criar essa Procedure stpBusca_String_Tabela, está mencionado esses dois erros abaixo… nas respectivas linhas citadas abaixo (Linha 94 – “WHILE (@contadorColunas & lt=@numeroColunas)” e Linha 105 – ” WHILE (@contadorLinhas &lt=@numeroLinhas)” )

Não sei o que está faltando ou passando…Alguém poderia me dar uma Luz!!

Msg 207, Level 16, State 1, Procedure stpBusca_String_Tabela, Line 94
Invalid column name ‘lt’.
Msg 207, Level 16, State 1, Procedure stpBusca_String_Tabela, Line 105
Invalid column name ‘lt’.

Aguardo!!

Responder
- Dirceu Resende disse:
  
  November 28, 2016 às 12:42
  
  Romildo,
  Bom dia.
  
  Alterei o plugin que utilizo para exibição de código-fonte no blog e com isso, alguns posts mais antigos tiveram alguns problemas com os caracteres < e >, que foi o erro que você teve.
  
  Atualizei esse post e agora o código vai funcionar normalmente.
  
  Obrigado pela visita!
  
  Responder
leandro disse:

December 2, 2015 às 15:22

Muito top essa procedure, show de bola. ajudou muito.

Responder
William Crudeli Junior disse:

September 10, 2014 às 11:49

Perfeito, muito bom o script, obrigado por compartilhar!!! Estava precisando procurar colunas que tinham ; para importar dados de um arquivo txt onde o delimitador de coluna era o ‘;’. Mas cai no problema de muitas colunas serem preenchidas com esse caracter. Valeuuuuu!

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Realizando uma busca textual em todas as colunas, de todas as tabelas no SQL Server

You may also like...

8 Responses

Deixe uma respostaCancelar resposta

You may also like...

SQL Server and Azure SQL: How to Delete or Update Data in Large Tables

SQL Server – Como converter uma string HTML para texto (Remover tags HTML) utilizando o CLR (C#)

Executando um comando em todos os databases da instância no SQL Server

8 Responses

Deixe uma respostaCancelar resposta