Como procurar um valor em qualquer coluna de qualquer tabela dentro de um database MS-SQL?

Existe uma maneira de procurar um valor (no meu caso, é um UID do tipo char (64)) dentro de qualquer coluna de qualquer tabela dentro de um database MS-SQL?

Estou sentado em frente a um database enorme, sem qualquer ideia de como as tabelas precisavam ser interligadas. Para descobrir isso, gostaria de listar todas as tabelas e colunas que contêm um determinado valor em qualquer linha. Isso é possível?

Uma maneira seria simplesmente despejar o database inteiro em um arquivo de texto e usar qualquer editor de texto para procurar o valor – mas isso seria uma grande dor se o database fosse muito grande.

Obrigado pela pergunta, pois este é um tópico realmente útil. Eu também vou usar isso agora por razões que incluem a que você apresenta. 🙂

Como pesquisar todas as colunas de todas as tabelas em um database para uma palavra-chave?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Andrew

-Edita, aqui está o T-SQL real, em caso de apodrecimento do link:

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END 

Tarde um, mas espero útil …

Eu tive o mesmo problema há alguns meses, mas consegui resolver isso usando ferramentas de terceiros.

Todas as ferramentas mencionadas abaixo são 100% gratuitas.

Eu usei o ApexSQL Search com bom êxito para pesquisar objects e dados em tabelas. Ele vem com vários outros resources, como diagramas de relacionamento e…

Eu estava um pouco lento em bancos de dados grandes (40GB TFS Database) embora…

insira a descrição da imagem aqui

Além disso, há também o pacote SSMS Tools que oferece muitos outros resources que são bastante úteis, embora não estejam diretamente relacionados à pesquisa de texto.

Eu ampliei o código, porque não me disseram o ‘ record number ‘, e devo reencontrá-lo.

 CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT -- Copyright @ 2012 Gyula Kulifai. All rights reserved. -- Extended By: Gyula Kulifai -- Purpose: To put key values, to exactly determine the position of search -- Resources: Anatoly Lubarsky -- Date extension: 19th October 2012 12:24 GMT -- Tested on: SQL Server 10.0.5500 (SQL Server 2008 SP3) CREATE TABLE #Results (TableName nvarchar(370), KeyValues nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) ,@TableShortName nvarchar(256) ,@TableKeys nvarchar(512) ,@SQL nvarchar(3830) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' -- Scan Tables SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) Set @TableShortName=PARSENAME(@TableName, 1) -- print @TableName + ';' + @TableShortName +'!' -- *** DEBUG LINE *** -- LOOK Key Fields, Set Key Columns SET @TableKeys='' SELECT @TableKeys = @TableKeys + '''' + QUOTENAME([name]) + ': '' + CONVERT(nvarchar(250),' + [name] + ') + ''' + ',' + ''' + ' FROM syscolumns WHERE [id] IN ( SELECT [id] FROM sysobjects WHERE [name] = @TableShortName) AND colid IN ( SELECT SIK.colid FROM sysindexkeys SIK JOIN sysobjects SO ON SIK.[id] = SO.[id] WHERE SIK.indid = 1 AND SO.[name] = @TableShortName) If @TableKeys<>'' SET @TableKeys=SUBSTRING(@TableKeys,1,Len(@TableKeys)-8) -- Print @TableName + ';' + @TableKeys + '!' -- *** DEBUG LINE *** -- Search in Columns WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) -- Set ColumnName IF @ColumnName IS NOT NULL BEGIN SET @SQL=' SELECT ''' + @TableName + ''' ,'+@TableKeys+' ,''' + @ColumnName + ''' ,LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 --Print @SQL -- *** DEBUG LINE *** INSERT INTO #Results Exec (@SQL) END -- IF ColumnName END -- While Table and Column END --While Table SELECT TableName, KeyValues, ColumnName, ColumnValue FROM #Results END 

Fonte: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Eu tenho uma solução de um tempo atrás que eu continuei melhorando. Também procura em colunas XML, se solicitado, ou pesquisa valores inteiros, se fornecer uma cadeia inteira somente.

 /* Reto Egeter, fullparam.wordpress.com */ DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */ SET @FullRowResult = 1 SET @FullRowResultRows = 3 SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */ SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */ SET @SearchStrInXML = 0 /* Searching XML data may be slow */ IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20)) SET NOCOUNT ON DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110) SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''') DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20)) WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME) AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ) IF @TableName IS NOT NULL BEGIN DECLARE @sql VARCHAR(MAX) SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2) AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1) AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ') AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)' INSERT INTO @ColumnNameTable EXEC (@sql) WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable) BEGIN PRINT @ColumnName SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue INSERT INTO #Results EXEC(@sql) IF @@ROWCOUNT > 0 IF @FullRowResult = 1 BEGIN SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' + ' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue EXEC(@sql) END DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName END END END SET NOCOUNT OFF SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results GROUP BY TableName, ColumnName, ColumnValue, ColumnType 

Encontrei uma solução bastante robusta em https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 , que achei que merecia ser destacada. Pesquisa colunas destes tipos: varchar, char, nvarchar, nchar, text. Funciona muito bem e suporta pesquisas específicas de tabelas, bem como vários termos de pesquisa.

Depois de tentar a solução @ regeter e ver que isso não resolveu meu problema quando eu estava procurando por uma chave estrangeira / primária para ver todas as tabelas / colunas onde ela existe, ela não funcionou. Depois de ler como ele falhou para outro que tentou usar um identificador único, fiz as modificações e aqui está o resultado atualizado: (funciona com int e guids … você verá como estender facilmente)

 CREATE PROC [dbo].[SearchAllTables_Like] ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('guid', 'int', 'char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END 

Existe um bom script disponível em http://www.reddyss.com/SQLDownloads.aspx

Para poder usá-lo em qualquer database, você pode criá-lo como em: http://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/

Não tenho certeza se existe outro caminho.

Para usá-lo, use algo assim:

 use name_of_database EXEC spUtil_SearchText 'value_searched', 0, 0