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

stpBusca_String_Tabela:

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'

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Autor: Dirceu Resende  https://www.dirceuresende.com/blog/realizando-uma-busca-textual-em-todas-as-colunas-de-todas-as-tabelas-de-todos-os-databases-no-sql-server/