Cerca una stringa in tutte le tabelle, righe e colonne di un DB

Mi sono perso in un grande database e non sono in grado di trovare da dove provengono i dati. Mi chiedevo se con SQL Server 2005 è ansible cercare una stringa in tutte le tabelle, righe e colonne di un database?

Qualcuno ha un’idea se è ansible e come?

Questo codice dovrebbe farlo in SQL 2005, ma alcuni avvertimenti:

  1. È LENTAMENTE RIDICAMENTE. L’ho provato su un piccolo database che avevo solo con una manciata di tabelle e ci sono voluti molti minuti per completarlo. Se il tuo database è così grande che non riesci a capirlo, probabilmente questo sarà comunque inutilizzabile.

  2. L’ho scritto a arm. Non ho inserito alcuna gestione degli errori e potrebbe esserci qualche altro inconveniente, soprattutto dal momento che non utilizzo spesso i cursori. Ad esempio, penso che ci sia un modo per aggiornare il cursore delle colonne invece di chiuderlo / deallocarlo / ricrearlo ogni volta.

Se non riesci a capire il database o non sai da dove viene la roba, probabilmente dovresti trovare qualcuno che lo faccia. Anche se è ansible trovare dove sono i dati, potrebbe essere duplicato da qualche parte o potrebbero esserci altri aspetti del database che non capisci. Se nessuno nella tua azienda comprende il database, allora sei in un bel casino.

DECLARE @search_string VARCHAR(100), @table_name SYSNAME, @table_schema SYSNAME, @column_name SYSNAME, @sql_string VARCHAR(2000) SET @search_string = 'Test' DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN tables_cur FETCH NEXT FROM tables_cur INTO @table_schema, @table_name WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL -- Only strings have this and they always have it OPEN columns_cur FETCH NEXT FROM columns_cur INTO @column_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + '''' EXECUTE(@sql_string) FETCH NEXT FROM columns_cur INTO @column_name END CLOSE columns_cur DEALLOCATE columns_cur FETCH NEXT FROM tables_cur INTO @table_schema, @table_name END CLOSE tables_cur DEALLOCATE tables_cur 

Ti suggerirei di trovarti uno strumento di terze parti per questo come ApexSQL Search (ci sono probabilmente anche altri là fuori, ma io uso questo perché è gratuito).

Se vuoi davvero passare in modo SQL puoi provare a usare stored procedure create da Sorna Kumar Muthuraj – il codice copiato è sotto. Basta eseguire questa stored procedure per tutte le tabelle del tuo schema (facile con dynamics SQL)

 CREATE PROCEDURE SearchTables @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /* Parameters and usage @Tablenames -- Provide a single table name or multiple table name with comma seperated. If left blank , it will check for all the tables in the database @SearchStr -- Provide the search string. Use the '%' to coin the search. EX : X%--- will give data staring with X %X--- will give data ending with X %X%--- will give data containig X @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. By default it is 0 and it will search. Samples : 1. To search data in a table EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' The above sample searches in table T1 with string containing TEST. 2. To search in a multiple table EXEC SearchTables @Tablenames = 'T2' ,@SearchStr = '%TEST%' The above sample searches in tables T1 & T2 with string containing TEST. 3. To search in a all table EXEC SearchTables @Tablenames = '%' ,@SearchStr = '%TEST%' The above sample searches in all table with string containing TEST. 4. Generate the SQL for the Select statements EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' ,@GenerateSQLOnly = 1 */ SET NOCOUNT ON DECLARE @CheckTableNames Table ( Tablename sysname ) DECLARE @SQLTbl TABLE ( Tablename SYSNAME ,WHEREClause VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO @CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' INSERT INTO @CheckTableNames EXEC(@sql) END INSERT INTO @SQLTbl ( Tablename,WHEREClause) SELECT SCh.name + '.' + ST.NAME, ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in ('varchar','char','nvarchar','nchar') AND SC.object_id = ST.object_id ORDER BY SC.name FOR XML PATH('') ) FROM SYS.tables ST JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.name JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id WHERE ST.name <> 'SearchTMP' GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM @SQLTbl WHERE WHEREClause IS NULL WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) BEGIN SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 IF @GenerateSQLOnly = 0 BEGIN IF OBJECT_ID('SearchTMP','U') IS NOT NULL DROP TABLE SearchTMP EXEC (@SQL) IF EXISTS(SELECT 1 FROM SearchTMP) BEGIN SELECT [email protected],* FROM SearchTMP END END ELSE BEGIN PRINT REPLICATE('-',100) PRINT @tmpTblname PRINT REPLICATE('-',100) PRINT replace(@sql,'INTO SearchTMP','') END UPDATE @SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname END SET NOCOUNT OFF go 

Sebbene le soluzioni presentate prima siano valide e funzionanti, offro umilmente un codice più pulito, più elegante e con prestazioni migliori, almeno per come lo vedo io.

In primo luogo, ci si potrebbe chiedere: perché mai qualcuno dovrebbe aver bisogno di un frammento di codice per cercare globalmente e in modo cieco una stringa? Ehi, hanno già inventato il testo completo, non lo sai?

La mia risposta: il mio lavoro principale è nei progetti di integrazione di sistemi, e scoprire dove sono scritti i dati è importante ogni volta che sto imparando un nuovo database non censurato, cosa che accade raramente.

Inoltre, il codice che presento è una versione ridotta di uno script più potente e pericoloso che cerca e SOSTITUISCE il testo nel database.

 CREATE TABLE #result( id INT IDENTITY, -- just for register seek order tblName VARCHAR(255), colName VARCHAR(255), qtRows INT ) go DECLARE @toLookFor VARCHAR(255) SET @toLookFor = '[input your search criteria here]' DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR SELECT '[' + usr.name + '].[' + tbl.name + ']' AS tblName, '[' + col.name + ']' AS colName, LOWER(typ.name) AS typName FROM sysobjects tbl INNER JOIN( syscolumns col INNER JOIN systypes typ ON typ.xtype = col.xtype ) ON col.id = tbl.id -- LEFT OUTER JOIN sysusers usr ON usr.uid = tbl.uid WHERE tbl.xtype = 'U' AND LOWER(typ.name) IN( 'char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext' ) ORDER BY tbl.name, col.colorder -- DECLARE @tblName VARCHAR(255) DECLARE @colName VARCHAR(255) DECLARE @typName VARCHAR(255) -- DECLARE @sql NVARCHAR(4000) DECLARE @crlf CHAR(2) SET @crlf = CHAR(13) + CHAR(10) OPEN cCursor FETCH cCursor INTO @tblName, @colName, @typName WHILE @@fetch_status = 0 BEGIN IF @typName IN('text', 'ntext') BEGIN SET @sql = '' SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf SET @sql = @sql + 'FROM ' + @tblName + @crlf SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf END ELSE BEGIN SET @sql = '' SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf SET @sql = @sql + 'FROM ' + @tblName + @crlf SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf END EXECUTE sp_executesql @sql, N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)', @tblName, @colName, @toLookFor FETCH cCursor INTO @tblName, @colName, @typName END SELECT * FROM #result WHERE qtRows > 0 ORDER BY id GO DROP TABLE #result go 

Se si “acquisiscono dati” da un’applicazione, la cosa sensata sarebbe utilizzare il profiler e il profilo del database durante l’esecuzione dell’applicazione. Traccia, quindi cerca i risultati per quella stringa.

Il componente aggiuntivo PACK di SSMS Tools (componente aggiuntivo) per Microsoft SQL Server Management Studio e Microsoft SQL Server Management Studio Express farà esattamente ciò di cui hai bisogno. Su un database più ampio ci vuole un po ‘di tempo per cercare, ma c’è da aspettarselo. Include anche un sacco di funzioni interessanti che dovrebbero essere incluse in SQL Server Management Studio. Provalo http://www.ssmstoolspack.com/

È necessario disporre di SP2 per SQL Server Management Studio installato per eseguire gli strumenti.

Ho adattato una sceneggiatura originariamente scritta da Narayana Vyas Kondreddi nel 2002 . Ho modificato la clausola where per verificare anche i campi text / ntext, utilizzando il suffisso piuttosto che il contrario. Ho anche modificato leggermente la tabella dei risultati. Irragionevolmente, ho cambiato i nomi delle variabili, e allineato come preferisco (senza mancare di rispetto a Mr. Kondretti). L’utente potrebbe voler cambiare i tipi di dati ricercati. Ho usato una tabella globale per consentire l’interrogazione di elaborazione intermedia, ma una tabella permanente potrebbe essere un modo più intelligente di andare.

 /* original script by Narayana Vyas Kondreddi, 2002 */ /* adapted by Oliver Holloway, 2009 */ /* these lines can be replaced by use of input parameter for a proc */ declare @search_string varchar(1000); set @search_string = 'what.you.are.searching.for'; /* create results table */ create table ##string_locations ( table_name varchar(1000), field_name varchar(1000), field_value varchar(8000) ) ; /* special settings */ set nocount on ; /* declare variables */ declare @table_name varchar(1000), @field_name varchar(1000) ; /* variable settings */ set @table_name = '' ; set @search_string = QUOTENAME('%' + @search_string + '%','''') ; /* for each table */ while @table_name is not null begin set @field_name = '' set @table_name = ( select MIN(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) from INFORMATION_SCHEMA.TABLES where table_type = 'BASE TABLE' and QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) > @table_name and OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0 ) /* for each string-ish field */ while (@table_name is not null) and (@field_name is not null) begin set @field_name = ( select MIN(QUOTENAME(column_name)) from INFORMATION_SCHEMA.COLUMNS where table_schema = PARSENAME(@table_name, 2) and table_name = PARSENAME(@table_name, 1) and data_type in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') and QUOTENAME(column_name) > @field_name ) /* search that field for the string supplied */ if @field_name is not null begin insert into ##string_locations exec( 'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name + 'from ' + @table_name + ' (nolock) ' + 'where patindex(' + @search_string + ',' + @field_name + ') > 0' /* patindex works with char & text */ ) end ; end ; end ; /* return results */ select table_name, field_name, field_value from ##string_locations (nolock) ; /* drop temp table */ --drop table ##string_locations ; 

Altre risposte già pubblicate potrebbero funzionare altrettanto bene o meglio, ma non li ho usati. Comunque, ho usato il seguente SQL che ho usato, e mi è stato di grande aiuto quando stavo provando a decodificare un grande sistema con un enorme database SQL Server (e molto poco organizzato).

Questo non è il mio codice. Vorrei poter accreditare l’autore originale, ma non riesco più a trovare il link all’articolo 🙁

 Use go declare @SearchChar varchar(8000) Set @SearchChar = -- Like 'A%', '11/11/2006' declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000) declare @ColumnName varchar(100),@TableName varchar(100) declare dbTable cursor for SELECT Distinct b.Name as TableName FROM sysobjects b WHERE b.type='u' and b.Name 'dtproperties' order by b.name open dbTable fetch next from dbTable into @TableName WHILE @@FETCH_STATUS = 0 BEGIN declare db cursor for SELECT c.Name as ColumnName FROM sysobjects b, syscolumns c WHERE C.id = b.id and b.type='u' and b.Name = @TableName order by b.name open db fetch next from db into @ColumnName set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+ ' ['+ @TableName + '].* FROM [' + @TableName + ']'+ ' WHERE ' set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where ' Set @CMDJoin = '' WHILE @@FETCH_STATUS = 0 BEGIN set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' [email protected] + ']) like ' + char(39) + @SearchChar + char(39) + ' OR ' fetch next from db into @ColumnName end close db deallocate db Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin ' Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3) Set @CMDMain = @CMDMain + ' End ' Print @CMDMain exec (@CMDMain) fetch next from dbTable into @TableName end close dbTable deallocate dbTable 

In realtà sono d’accordo con MikeW (+1) è meglio usare profiler per questo caso.

Ad ogni modo, se hai davvero bisogno di prendere tutte le colonne varchar (n) in db e fare una ricerca. Vedi sotto. Suppongo di usare INFORMATION_SCHEMA.Tables + SQL dinamico. La semplice ricerca:

 DECLARE @SearchText VARCHAR(100) SET @SearchText = '12' DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSV VARCHAR(2000), SQL VARCHAR(4000)) INSERT INTO @Tables (TableName, ColumnNamesCSV) SELECT T.TABLE_NAME AS TableName, ( SELECT C.Column_Name + ',' FROM INFORMATION_SCHEMA.Columns C WHERE T.TABLE_NAME = C.TABLE_NAME AND C.DATA_TYPE IN ('nvarchar','varchar') FOR XML PATH('') ) FROM INFORMATION_SCHEMA.Tables T DELETE FROM @Tables WHERE ColumnNamesCSV IS NULL INSERT INTO @Tables (N, TableName, ColumnNamesCSV) SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSV FROM @Tables DELETE FROM @Tables WHERE N IS NULL UPDATE @Tables SET ColumnNamesCSV = SUBSTRING(ColumnNamesCSV, 0, LEN(ColumnNamesCSV)) UPDATE @Tables SET SQL = 'SELECT * FROM ['+TableName+'] WHERE '''[email protected]+''' IN ('+ColumnNamesCSV+')' DECLARE @C INT, @I INT, @SQL VARCHAR(4000) SELECT @I = 1, @C = COUNT(1) FROM @Tables WHILE @I <= @C BEGIN SELECT @SQL = SQL FROM @Tables WHERE N = @I SET @I = @I+1 EXEC(@SQL) END 

e uno con la clausola LIKE:

 DECLARE @SearchText VARCHAR(100) SET @SearchText = '12' DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSVLike VARCHAR(2000), LIKESQL VARCHAR(4000)) INSERT INTO @Tables (TableName, ColumnNamesCSVLike) SELECT T.TABLE_NAME AS TableName, ( SELECT C.Column_Name + ' LIKE ''%'[email protected]+'%'' OR ' FROM INFORMATION_SCHEMA.Columns C WHERE T.TABLE_NAME = C.TABLE_NAME AND C.DATA_TYPE IN ('nvarchar','varchar') FOR XML PATH('')) FROM INFORMATION_SCHEMA.Tables T DELETE FROM @Tables WHERE ColumnNamesCSVLike IS NULL INSERT INTO @Tables (N, TableName, ColumnNamesCSVLike) SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSVLike FROM @Tables DELETE FROM @Tables WHERE N IS NULL UPDATE @Tables SET ColumnNamesCSVLike = SUBSTRING(ColumnNamesCSVLike, 0, LEN(ColumnNamesCSVLike)-2) UPDATE @Tables SET LIKESQL = 'SELECT * FROM ['+TableName+'] WHERE '+ColumnNamesCSVLike DECLARE @C INT, @I INT, @LIKESQL VARCHAR(4000) SELECT @I = 1, @C = COUNT(1) FROM @Tables WHILE @I <= @C BEGIN SELECT @LIKESQL = LIKESQL FROM @Tables WHERE N = @I SET @I = @I +1 EXEC(@LIKESQL) END 

@NLwino, buona domanda con alcuni errori per l’utilizzo delle parole chiave. Ho dovuto modificarlo un po ‘per avvolgere le parole chiave con [] e anche cercare colonne char e ntext.

  DECLARE @searchstring NVARCHAR(255) SET @searchstring = '%WDB1014%' DECLARE @sql NVARCHAR(max) SELECT @sql = STUFF(( SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tbl, ''' + COLUMN_NAME + ''' AS col, [' + COLUMN_NAME + '] AS val' + ' FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''' + @searchstring + '''' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE in ('nvarchar', 'varchar', 'char', 'ntext') FOR XML PATH('') ) ,1, 11, '') Exec (@sql) 

L’ho eseguito su un database da 2,5 GB e sono tornati in 51 secondi

Questo non utilizza cursori o qualcosa del genere, solo una query dynamic.

Si noti inoltre che questo utilizza LIKE . Dal momento che quello che è successo è quello di cui avevo bisogno. Funziona con tutti gli schemi, tutte le tabelle e solo le colonne di query che sono NVARCHAR o VARCHAR anche se hanno UDDT.

 DECLARE @searchstring NVARCHAR(255) SET @searchstring = '%searchstring%' DECLARE @sql NVARCHAR(max) SELECT @sql = STUFF(( SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tablename, ''' + COLUMN_NAME + ''' AS columnname, ' + COLUMN_NAME + ' AS valuename' + ' FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' WHERE ' + COLUMN_NAME + ' LIKE ''' + @searchstring + '''' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE in ('nvarchar', 'varchar') FOR XML PATH('') ) ,1, 11, '') EXEC(@sql) 

L’output fornisce la tabella, la colonna e il valore. Il tempo di esecuzione su un piccolo database era di ~ 3 secondi, aveva circa 3000 risultati.

 /* This procedure is for finding any string or date in all tables if search string is date, its format should be yyyy-MM-dd eg. 2011-07-05 */ -- ================================================ -- Exec SearchInTables 'f6f56934-a5d4-4967-80a1-1a2223b9c7b1' -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:  -- Create date:  -- Description:  -- ============================================= ALTER PROCEDURE SearchInTables @myValue nvarchar(1000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @searchsql nvarchar(max) DECLARE @table_name nvarchar(1000) DECLARE @Schema_name nvarchar(1000) DECLARE @ParmDefinition nvarchar(500) DECLARE @XMLIn nvarchar(max) SET @ParmDefinition = N'@XMLOut varchar(max) OUTPUT' SELECT A.name,b.name FROM sys.tables A INNER JOIN sys.schemas B ON A.schema_id=B.schema_id WHERE A.name like 'tbl_Tax_Sections' DECLARE tables_cur CURSOR FOR SELECT A.name,b.name FOM sys.tables A INNER JOIN sys.schemas B ON A.schema_id=B.schema_id WHERE A.type = 'U' OPEN tables_cur FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @searchsql ='SELECT @XMLOut=(SELECT PATINDEX(''%'+ @myValue+ '%''' SET @searchsql [email protected] + ', (SELECT * FROM '[email protected]_name+'.'[email protected]_name+' FOR XML AUTO) ))' --print @searchsql EXEC sp_executesql @searchsql, @ParmDefinition, @[email protected] OUTPUT --print @XMLIn IF @XMLIn <> 0 PRINT @Schema_name+'.'[email protected]_name FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name END CLOSE tables_cur DEALLOCATE tables_cur RETURN END GO 

Per “trovare da dove proviene il dato che ottengo”, è ansible avviare SQL Profiler, avviare il report o l’applicazione e verranno visualizzate tutte le query emesse sul database.

Penso che questo possa essere un modo più semplice per trovare una stringa in tutte le righe del database, senza usare cursori e FOR XML -.

 CREATE PROCEDURE SPFindAll (@find VARCHAR(max) = '') AS BEGIN SET NOCOUNT ON; -- DECLARE @query VARCHAR(max) = '' SELECT @query = @query + CASE WHEN @query = '' THEN '' ELSE ' UNION ALL ' END + 'SELECT ''' + s.name + ''' As schemaName, ''' + t.name + ''' As tableName, ''' + c.name + ''' As ColumnName, [' + c.name + '] COLLATE DATABASE_DEFAULT As [Data] FROM [' + s.name + '].[' + t.name + '] WHERE [' + c.name + '] Like ''%' + @find + '%''' FROM sys.schemas s INNER JOIN sys.tables t ON s.[schema_id] = t.[schema_id] INNER JOIN sys.columns c ON t.[object_id] = c.[object_id] INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE ty.name LIKE '%char' EXEC(@query) END 

Creando questa stored procedure puoi eseguirla per qualsiasi stringa che vuoi trovare in questo modo:

 EXEC SPFindAll 'Hello World' 

Il risultato sarà così:

 schemaName | tableName | columnName | Data -----------+-----------+------------+----------------------- schema1 | Table1 | Column1 | Hello World schema1 | Table1 | Column1 | Hello World! schema1 | Table2 | Column1 | I say "Hello World". schema1 | Table2 | Column2 | Hello World 

Oppure, puoi usare la mia query qui, dovrebbe essere più semplice quindi dover creare sProcs per ogni DB che vuoi cercare: FullParam SQL Blog

 /* 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 (*) Conteggio AS FROM #Results GROUP BY TableName, ColumnName, ColumnValue, ColumnType