Mais uma da série canivete suíço. 😉
Como mais uma necessidade “doida” do dia a dia que vivemos, foi preciso procurar em todas as tabelas de um banco de dados SQL Server que continham uma determinada string. Pois bem, com várias opções em mãos, a menos complicada de enviar para um ambiente de produção sem a necessidade de um Application Server era uma query SQL que fizesse o trabalho.
Depois de muito suor e testes, o resultado foi a query abaixo:
DECLARE @SQL VARCHAR(8000) DECLARE @filtro VARCHAR(200) DECLARE @filtro_www VARCHAR(200) -- inicia a declaração do sql SET @SQL = '' SET @filtro = '%texto_procurado%' SELECT tabelas.name AS Tabela ,colunas.name AS Coluna ,tipos.name AS Tipo ,colunas.length AS Tamanho INTO #result FROM sysobjects tabelas INNER JOIN syscolumns colunas ON colunas.id = tabelas.id -- INNER JOIN systypes tipos ON tipos.xtype = colunas.xtype WHERE tabelas.xtype = 'u' AND -- colocar aqui os tipos de coluna que serão buscados tipos.name IN('text', 'ntext', 'varchar', 'nvarchar') -- cursor para varrer as tabelas DECLARE cTabelas cursor LOCAL fast_forward FOR SELECT DISTINCT Tabela FROM #result DECLARE @nomeTabela VARCHAR(255) OPEN cTabelas fetch NEXT FROM cTabelas INTO @nomeTabela while @@fetch_status = 0 BEGIN -- cursor para varrer as colunas da tabela corrente DECLARE cColunas cursor LOCAL fast_forward FOR SELECT Coluna, Tipo, Tamanho FROM #result WHERE Tabela = @nomeTabela DECLARE @nomeColuna VARCHAR(255) DECLARE @tipoColuna VARCHAR(255) DECLARE @tamanhoColuna VARCHAR(255) OPEN cColunas -- monta as colunas da cláusula select fetch NEXT FROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna while @@fetch_status = 0 BEGIN -- cria a declaração da variável SET @SQL = 'declare @hasresults bit' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) -- cria o select SET @SQL = @SQL + 'select' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(9) + '''' + @nomeTabela + ''' AS NomeTabela' SET @SQL = @SQL + CHAR(9) + ',' + @nomeColuna + CHAR(13) + CHAR(10) -- adiciona uma coluna com o tipo e o tamanho do campo SET @SQL = @SQL + CHAR(9) + ',' + '''' + @tipoColuna + ''' AS ''' + @nomeColuna + '_Tipo''' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(9) + ',' + 'DATALENGTH(' + @nomeColuna + ') AS ''' + @nomeColuna + '_Tamanho_Ocupado''' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(9) + ',' + '''' + @tamanhoColuna + ''' AS ''' + @nomeColuna + '_Tamanho_Maximo''' + CHAR(13) + CHAR(10) -- define a tabela temporária (#result) SET @SQL = @SQL + 'into' + CHAR(13) + CHAR(10) + CHAR(9) + '#result_' + @nomeTabela + CHAR(13) + CHAR(10) -- adiciona a cláusula from SET @SQL = @SQL + 'from' + CHAR(13) + CHAR(10) + CHAR(9) + @nomeTabela + CHAR(13) + CHAR(10) -- inicia a montagem do where SET @SQL = @SQL + 'where' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(9) + @nomeColuna + ' like ''' + @filtro + '''' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'select @hasresults = count(*) from #result_' + @nomeTabela + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'if @hasresults > 0' SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'begin' SET @SQL = @SQL + CHAR(13) + CHAR(10) + CHAR(9) + 'select * from #result_' + @nomeTabela SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'end' + CHAR(13) + CHAR(10) SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'drop table #result_' + @nomeTabela SET @SQL = @SQL + CHAR(13) + CHAR(10) fetch NEXT FROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna -- descomente a linha abaixo para ver o SQL produzido no janela de Messages -- print @sql EXEC(@SQL) SET @SQL = '' END close cColunas deallocate cColunas fetch NEXT FROM cTabelas INTO @nomeTabela END close cTabelas deallocate cTabelas DROP TABLE #result |
O que ela faz exatamente?
- Busca no sysobjects todos as tabelas existentes no banco de dados.
- Faz o mesmo na tabela syscolumns para obter o nome da coluna, o tamanho e o tipo delas, conforme configuração
- Por fim, verifica se o texto existe na tabela e campo informado
- Se houver, apresente um resultset completo com o nome da tabela, o dado, o tipo do campo e tamanho
- A query foi testada no SQL Server 2000 e 2005, funcionando perfeitamente e ambos o casos.
É importante notar que não foram colocadas aqui considerações de desempenho ou o uso do recurso fulltext, sendo o objetivo principal resolver o problema de encontrar o texto desejado.
Bom proveito!
Show
Uma duvida, se eu quiser apagar apenas o que foi encontrado que acrescentar neste codigo. Grato.
Emerson,
Não entendi a pergunta. De qualquer forma, melhor discutirmos no fórum:
http://www.tomasvasquez.com.br/forum
Nos vemos lá!
Abraços
Tomás