Views: 13.516 views
Hello dear readers.
Good Morning!
Today I want to talk about a need in SQL Server where I needed to look in which columns, from which tables, a record was recorded. To solve this problem, I created a Stored Procedure that performs this search:
stpSearch_String_Table:
| 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 |
Usage examples:
| 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' |
![SQL Server - The activated proc '[dbo]. [Sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue'](http://dirceuresende.com/wp-content/themes/boxcard/img/thumb-medium.png)
Very good. Worked perfectly.
I ran the script and gave the following error:
"Msg 2714, Level 16, State 3, Procedure stpBusca_String_Table, Line 146
There is already an object named 'stpBusca_String_Tabela' in the database. ”
Valmir, this error happened because you must have run the create command twice and the bank warned that this object already exists 🙂
When trying to create this Procedure stpBusca_String_Tabela, these two errors are mentioned below… in the respective lines mentioned below (Line 94 - “WHILE (@contadorColunas & [email protected]) ”And Line 105 -” WHILE (@contadorLinhas &[email protected]) ”)
I don't know what is missing or happening ... Could someone give me a Light !!
Msg 207, Level 16, State 1, Procedure stpSearch_String_Table, Line 94
Invalid column name 'lt'.
Msg 207, Level 16, State 1, Procedure stpSearch_String_Table, Line 105
Invalid column name 'lt'.
Wait!!
Romildo,
Good day.
I changed the plugin I use to display source code on the blog and with that, some older posts had some problems with the <and> characters, which was the error you had.
I updated this post and now the code will work normally.
Thanks for the visit!
Very top this procedure, ball show. it helped a lot.
Perfect, very good script, thanks for sharing !!! I needed to look for columns they had; to import data from a txt file where the column delimiter was ';'. But it falls into the problem that many columns are filled with this character. Thanks!