Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.456 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 14 de junho de 2014
  • 7
  • Banco de Dados Desenvolvimento de Query DMVs e Views de Catálogo SQL Server

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

Visualizações: 8.567
Tempo de Leitura: 3 minutos

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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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 [CLR]
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

Exemplos de utilização:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Realiza uma busca pela palavra "Dirceu" em todas as colunas e tabelas do database Clientes
EXEC CLR.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 CLR.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 CLR.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 CLR.dbo.stpBusca_String_Tabela
    @Ds_Texto = 'Dirceu' , -- varchar(100)
    @Ds_Banco = 'Clientes' -- varchar(max),
    @Ds_Tabela_Destino = '##Resultado'

Tags: banco de dadosbuscafulltextsearchsql serverstringtexto

You may also like...

  • Os comandos SET do SQL Server

  • SQL Server – Como exportar o assembly de um CLR como DLL e fazer engenharia reversa para código-fonte C#

  • SQL Server Management Studio (SSMS) – Como ativar o tema Dark (Dark Theme Support)

  • Next Importando arquivos CSV para o banco de dados SQL Server
  • Previous Operações com arquivos utilizando OLE Automation no SQL Server

7 Responses

  • Comments7
  • Pingbacks0
  1. Renan disse:
    5 de julho de 2018 às 11:48

    Muito bom. Funcionou perfeitamente.

    Responder
  2. Valmir Pinheiro disse:
    16 de fevereiro de 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:
      16 de fevereiro de 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
  3. Romildo Pereira disse:
    28 de novembro de 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 & [email protected])” e Linha 105 – ” WHILE (@contadorLinhas &[email protected])” )

    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:
      28 de novembro de 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
  4. leandro disse:
    2 de dezembro de 2015 às 15:22

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

    Responder
  5. William Crudeli Junior disse:
    10 de setembro de 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

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.