Come posso trovare un valore in un database di SQL Server?

Dato un #, come faccio a scoprire in quale tabella e colonna si può trovare all’interno?

Non mi interessa se è veloce, deve solo funzionare.

Questo potrebbe aiutarti . – da Narayana Vyas. Cerca tutte le colonne di tutte le tabelle in un determinato database. L’ho usato prima e funziona.

Questo è lo Stored Proc dal link precedente – l’unica modifica che ho apportato è stata la sostituzione della tabella temporanea per una variabile table in modo da non dover ricordare di rilasciarlo ogni volta.

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 DECLARE @Results TABLE(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 

Se hai bisogno di eseguire tale ricerca solo una volta, puoi probabilmente andare con uno qualsiasi degli script già mostrati qui. Altrimenti, consiglierei di usare la ricerca ApexSQL per questo. È un add-on gratuito SSMS e mi ha davvero risparmiato un sacco di tempo.

Prima di eseguire uno degli script di cui sopra, è necessario personalizzarlo in base al tipo di dati che si desidera utilizzare. Se sai che stai cercando una colonna datetime, non c’è bisogno di cercare tra le colonne di nvarchar. Ciò velocizzerà tutte le query sopra.

Sulla base della risposta di bnkdev ho modificato il codice di Narayana per cercare tutte le colonne anche quelle numeriche.

Funzionerà più lentamente, ma questa versione in realtà trova tutte le corrispondenze non solo quelle trovate nelle colonne di testo.

Non posso ringraziare abbastanza questo ragazzo. Mi ha salvato giorni di ricerca a mano!

 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 QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END 

Questa è la mia opinione indipendente su questa domanda che uso per il mio lavoro. Funziona in SQL2000 e versioni successive, consente i caratteri jolly, il filtro colonne e ricerca la maggior parte dei tipi di dati normali.

Una descrizione di pseudo-codice potrebbe essere select * from * where any like 'foo'

 -------------------------------------------------------------------------------- -- Search all columns in all tables in a database for a string. -- Does not search: image, sql_variant or user-defined types. -- Exact search always for money and smallmoney; no wildcards for matching these. -------------------------------------------------------------------------------- declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+ declare @ColumnName sysname -------------------------------------------------------------------------------- -- SET THESE! -------------------------------------------------------------------------------- set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols -------------------------------------------------------------------------------- -- END SET -------------------------------------------------------------------------------- set nocount on declare @TabCols table ( id int not null primary key identity , table_schema sysname not null , table_name sysname not null , column_name sysname not null , data_type sysname not null ) insert into @TabCols (table_schema, table_name, column_name, data_type) select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE from INFORMATION_SCHEMA.TABLES t join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME where 1 = 1 and t.TABLE_TYPE = 'base table' and c.DATA_TYPE not in ('image', 'sql_variant') and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end order by c.TABLE_NAME, c.ORDINAL_POSITION declare @table_schema sysname , @table_name sysname , @column_name sysname , @data_type sysname , @exists nvarchar(4000) -- Can be max for SQL2005+ , @sql nvarchar(4000) -- Can be max for SQL2005+ , @where nvarchar(4000) -- Can be max for SQL2005+ , @run nvarchar(4000) -- Can be max for SQL2005+ while exists (select null from @TabCols) begin select top 1 @table_schema = table_schema , @table_name = table_name , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0' , @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0' , @where = '' from @TabCols order by id while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin select top 1 @column_name = column_name , @data_type = data_type from @TabCols where table_schema = @table_schema and table_name = @table_name order by id -- Special case for money if @data_type in ('money', 'smallmoney') begin if isnumeric(@SearchTerm) = 1 begin set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards end end -- Special case for xml else if @data_type = 'xml' begin set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + '''' end -- Special case for date else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + '''' end -- Search all other types else begin set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + '''' end delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name end set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end' print @run exec sp_executesql @run end set nocount off 

Non lo metto sotto forma di proc perché non voglio mantenerlo su centinaia di DB ed è comunque un lavoro ad hoc. Non esitate a commentare bug-correzioni.

Ho ottimizzato la risposta di Allain Lalonde ( https://stackoverflow.com/a/436676/412368 ). I valori numerici sono ancora supportati. Dovrebbe essere circa 4-5 volte più veloce (1:03 vs 4:30), testato su un desktop con un database da 7 GB. http://developer.azurewebsites.net/2015/01/mssql-searchalltables/

 IF OBJECT_ID ('dbo.SearchAllTables', 'P') IS NOT NULL DROP PROCEDURE dbo.SearchAllTables; GO 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 -- Customized and modified: 2014-01-21 -- Tested on: SQL Server 2008 R2 DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256) DECLARE @ColumnName nvarchar(128) DECLARE @DataType nvarchar(128) DECLARE @SearchStr2 nvarchar(110) DECLARE @SearchDecimal decimal(38,19) DECLARE @Query nvarchar(4000) SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(decimal(38,19), @SearchStr) ELSE NULL END PRINT '@SearchStr2: ' + @SearchStr2 PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS nvarchar) SET @TableName = '' 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)) DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint', 'tinyint', 'numeric', 'decimal') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) SET @DataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND QUOTENAME(COLUMN_NAME) = @ColumnName ) PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')' IF @ColumnName IS NOT NULL BEGIN IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal') BEGIN IF @SearchDecimal IS NOT NULL BEGIN SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS nvarchar(110)), 3630) ' + 'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS nvarchar) PRINT ' ' + @Query INSERT INTO @Results EXEC (@Query) END END ELSE BEGIN SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' + 'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 PRINT ' ' + @Query INSERT INTO @Results EXEC (@Query) END END END END SELECT ColumnName, ColumnValue FROM @Results END 

Grazie per la sceneggiatura davvero utile.

Potrebbe essere necessario aggiungere la seguente modifica al codice se le tabelle hanno campi non convertibili:

 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 NOT IN ('text', 'image', 'ntext') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 

Chris

È il mio modo di risolvere questa domanda. Testato su SQLServer2008R2

 CREATE PROC SearchAllTables @SearchStr nvarchar(100) AS BEGIN DECLARE @dml nvarchar(max) = N'' IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results CREATE TABLE dbo.#Results ([tablename] nvarchar(100), [ColumnName] nvarchar(100), [Value] nvarchar(max)) SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%''' FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant') INSERT dbo.#Results EXEC sp_executesql @dml SELECT * FROM dbo.#Results END 

Ho una soluzione da un po ‘di tempo che ho continuato a migliorare. Cerca anche all’interno di colonne XML se gli viene richiesto di farlo, o cerca valori interi se fornisce una stringa intera solo.

 /* 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 

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

Qui, soluzione molto dolce e piccola:

 1) create a store procedure: create procedure get_table @find_str varchar(50) as begin declare @col_name varchar(500), @tab_name varchar(500); declare @find_tab TABLE(table_name varchar(100), column_name varchar(100)); DECLARE tab_col cursor for select C.name as 'col_name', T.name as tab_name from sys.tables as T left outer join sys.columns as C on C.object_id=T.object_id left outer join sys.types as TP on C.system_type_id=TP.system_type_id where type='U' and TP.name in('text','ntext','varchar','char','nvarchar','nchar'); open tab_col fetch next from tab_col into @col_name, @tab_name while @@FETCH_STATUS = 0 begin insert into @find_tab exec('select ''' + @tab_name + ''',''' + @col_name + ''' from ' + @tab_name + ' where ' + @col_name + '=''' + @find_str + ''' group by ' + @col_name + ' having count(*)>0'); fetch next from tab_col into @col_name, @tab_name; end CLOSE tab_col; DEALLOCATE tab_col; select table_name, column_name from @find_tab; end 

==========================

 2) call procedure by calling store procedure: exec get_table 'serach_string'; 

Probabilmente dovrai scrivere un breve script per interrogare i metadati (in questo caso un elenco di tabelle / colonne) dal database ed emettere una serie di istruzioni selezionate alla ricerca del valore.

Un altro modo usando JOIN e CURSOR:

 USE My_Database; -- Store results in a local temp table so that. I'm using a -- local temp table so that I can access it in SP_EXECUTESQL. create table #tmp ( tbl nvarchar(max), col nvarchar(max), val nvarchar(max) ); declare @tbl nvarchar(max); declare @col nvarchar(max); declare @q nvarchar(max); declare @search nvarchar(max) = 'my search key'; -- Create a cursor on all columns in the database declare c cursor for SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME -- For each table and column pair, see if the search value exists. open c fetch next from c into @tbl, @col while @@FETCH_STATUS = 0 begin -- Look for the search key in current table column and if found add it to the results. SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%''' EXEC SP_EXECUTESQL @q fetch next from c into @tbl, @col end close c deallocate c -- Get results select * from #tmp -- Remove local temp table. drop table #tmp 

Ho scritto una volta uno strumento per me stesso per fare esattamente quella cosa:

a7 SqlTools

È gratuito e open-source:

collegamento github

Potrebbe essere necessario creare un indice invertito per il tuo database. Si è assicurato di essere piuttosto veloce.

 -- exec pSearchAllTables 'M54*' ALTER PROC pSearchAllTables (@SearchStr NVARCHAR(100)) AS BEGIN -- A procedure to search all tables in a database for a value -- Note: Use * or % for wildcard DECLARE @Results TABLE([Schema.Table.ColumnName] NVARCHAR(370), ColumnValue NVARCHAR(3630)) SET NOCOUNT ON DECLARE @TableName NVARCHAR(256) = '' , @ColumnName NVARCHAR(128) , @SearchStr2 NVARCHAR(110) = QUOTENAME(REPLACE(@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 [Schema.Table.ColumnName] , ColumnValue FROM @Results GROUP BY [Schema.Table.ColumnName] , ColumnValue END