Visualizações: 13.503 views
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:
|
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 [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 |
Exemplos de utilização:
|
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 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' |
Muito bom. Funcionou perfeitamente.
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.”
Valmir, esse erro aconteceu porque você deve ter executado o comando de create duas vezes e o banco avisou que esse objeto já existe 🙂
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!!
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!
Muito top essa procedure, show de bola. ajudou muito.
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!