Rechercher une chaîne dans toutes les tables, lignes et colonnes d’un DB

Je suis perdu dans une grande firebase database et je ne suis pas en mesure de trouver d’où proviennent les données. Je me demandais s’il est possible avec SQL Server 2005 de rechercher une chaîne dans toutes les tables, lignes et colonnes d’une firebase database?

Est-ce que quelqu’un a une idée si c’est possible et comment?

Ce code devrait le faire dans SQL 2005, mais quelques avertissements:

  1. C’est très lent. Je l’ai testé sur une petite firebase database que j’ai avec seulement quelques tables et cela a pris plusieurs minutes. Si votre firebase database est si volumineuse que vous ne pouvez pas la comprendre, cela sera probablement inutilisable de toute façon.

  2. J’ai écrit ceci Je n’ai pas mis en place de gestion des erreurs et il pourrait y avoir un autre flou surtout que je n’utilise pas souvent les curseurs. Par exemple, je pense qu’il est possible d’actualiser le curseur des colonnes au lieu de le fermer / désallouer / recréer à chaque fois.

Si vous ne comprenez pas la firebase database ou si vous ne savez pas d’où proviennent les produits, vous devriez probablement trouver quelqu’un qui le fait. Même si vous pouvez trouver où se trouvent les données, elles peuvent être dupliquées quelque part ou il se peut que vous ne compreniez pas d’autres aspects de la firebase database. Si personne dans votre entreprise ne comprend la firebase database, alors vous êtes dans un gros désordre.

DECLARE @search_ssortingng VARCHAR(100), @table_name SYSNAME, @table_schema SYSNAME, @column_name SYSNAME, @sql_ssortingng VARCHAR(2000) SET @search_ssortingng = '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 ssortingngs 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_ssortingng = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_ssortingng + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + '''' EXECUTE(@sql_ssortingng) 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 

Je vous suggère de vous trouver un outil tiers pour cela comme ApexSQL Search (il y en a probablement d’autres, mais j’utilise celui-ci car c’est gratuit).

Si vous voulez vraiment utiliser le mode SQL, vous pouvez essayer d’utiliser la procédure stockée créée par Sorna Kumar Muthuraj – le code copié est ci-dessous. Il suffit d’exécuter cette procédure stockée pour toutes les tables de votre schéma (facile avec SQL dynamic)

 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 ssortingng. 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 ssortingng containing TEST. 2. To search in a multiple table EXEC SearchTables @Tablenames = 'T2' ,@SearchStr = '%TEST%' The above sample searches in tables T1 & T2 with ssortingng containing TEST. 3. To search in a all table EXEC SearchTables @Tablenames = '%' ,@SearchStr = '%TEST%' The above sample searches in all table with ssortingng 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 Tablename=@tmpTblname,* 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 

Bien que les solutions présentées soient valables et efficaces, je propose humblement un code plus propre, plus élégant et plus performant, du moins tel que je le vois.

Tout d’abord, on peut se demander: pourquoi quelqu’un aurait-il besoin d’un extrait de code pour rechercher globalement et aveuglément une chaîne? Hé, ils ont déjà inventé le texte intégral, tu ne sais pas?

Ma réponse: mon travail principal concerne les projets d’intégration de systèmes, et il est important de découvrir où sont écrites les données lorsque j’apprends une nouvelle firebase database non documentée, ce qui arrive rarement.

En outre, le code que je présente est une version simplifiée d’un script plus puissant et plus dangereux qui recherche et remplace le texte dans toute la firebase 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 

Si vous “obtenez des données” à partir d’une application, la solution judicieuse consisterait à utiliser le profileur et à profiler la firebase database pendant l’exécution de l’application. Tracez-le, puis recherchez les résultats pour cette chaîne.

Le complément SSMS Tools PACK (module complémentaire) pour Microsoft SQL Server Management Studio et Microsoft SQL Server Management Studio Express fera exactement ce dont vous avez besoin. Sur une firebase database plus importante, la recherche prend un certain temps, mais il faut s’y attendre. Il inclut également une tonne de fonctionnalités intéressantes qui devraient être incluses avec SQL Server Management Studio en premier lieu. Essayez-le http://www.ssmstoolspack.com/

Vous devez avoir SP2 pour SQL Server Management Studio installé pour exécuter les outils.

J’ai adapté un script écrit à l’origine par Narayana Vyas Kondreddi en 2002 . J’ai changé la clause where pour vérifier également les champs text / ntext, en utilisant patindex plutôt que comme. J’ai également changé légèrement le tableau de résultats. De manière déraisonnable, j’ai changé les noms de variables et je les ai alignés selon mes préférences (sans manquer de respect à M. Kondretti). L’utilisateur peut vouloir changer les types de données recherchés. J’ai utilisé un tableau global pour permettre d’interroger le traitement intermédiaire, mais une table permanente pourrait être une solution plus intelligente.

 /* 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_ssortingng varchar(1000); set @search_ssortingng = 'what.you.are.searching.for'; /* create results table */ create table ##ssortingng_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_ssortingng = QUOTENAME('%' + @search_ssortingng + '%','''') ; /* 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 ssortingng-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 ssortingng supplied */ if @field_name is not null begin insert into ##ssortingng_locations exec( 'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name + 'from ' + @table_name + ' (nolock) ' + 'where patindex(' + @search_ssortingng + ',' + @field_name + ') > 0' /* patindex works with char & text */ ) end ; end ; end ; /* return results */ select table_name, field_name, field_value from ##ssortingng_locations (nolock) ; /* drop temp table */ --drop table ##ssortingng_locations ; 

D’autres réponses déjà postées peuvent fonctionner aussi bien ou mieux, mais je ne les ai pas utilisées. Cependant, j’ai utilisé le code SQL suivant et cela m’a vraiment aidé lorsque j’ai essayé de désosser un grand système avec une firebase database SQL Server énorme (et très désorganisée).

Ce n’est pas mon code. J’aimerais pouvoir créditer l’auteur original, mais je ne trouve plus le lien vers l’article 🙁

 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),[' +@ColumnName + ']) 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 

En fait, je suis d’accord avec MikeW (+1), il est préférable d’utiliser le profileur pour ce cas.

Quoi qu’il en soit, si vous devez vraiment saisir toutes les colonnes varchar dans db et effectuer une recherche. Voir ci-dessous. Je suppose d’utiliser INFORMATION_SCHEMA.Tables + SQL dynamic. La recherche simple:

 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 '''+@SearchText+''' 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 

et un avec la clause 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 ''%'+@SearchText+'%'' 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, yery bonne requête avec quelques erreurs pour l’utilisation des mots clés. J’ai dû le modifier un peu pour envelopper les mots-clés avec [] et regarder aussi les colonnes char et ntext.

  DECLARE @searchssortingng NVARCHAR(255) SET @searchssortingng = '%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 ''' + @searchssortingng + '''' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE in ('nvarchar', 'varchar', 'char', 'ntext') FOR XML PATH('') ) ,1, 11, '') Exec (@sql) 

Je l’ai couru sur la firebase database de 2,5 Go et il est revenu en 51 secondes

Cela n’utilise aucun curseur ou quelque chose comme ça, juste une requête dynamic.

Notez également que cela utilise LIKE . Depuis que c’était ce que j’avais besoin. Il fonctionne pour tous les schémas, toutes les tables et interroge uniquement les colonnes NVARCHAR ou VARCHAR même si elles ont UDDT.

 DECLARE @searchssortingng NVARCHAR(255) SET @searchssortingng = '%searchssortingng%' 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 ''' + @searchssortingng + '''' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE in ('nvarchar', 'varchar') FOR XML PATH('') ) ,1, 11, '') EXEC(@sql) 

La sortie vous donne le tableau, la colonne et la valeur. Le temps d’exécution sur une petite firebase database était d’environ 3 secondes, avec environ 3000 résultats.

 /* This procedure is for finding any ssortingng or date in all tables if search ssortingng 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 =@searchsql + ', (SELECT * FROM '+@Schema_name+'.'+@table_name+' FOR XML AUTO) ))' --print @searchsql EXEC sp_executesql @searchsql, @ParmDefinition, @XMLOut=@XMLIn OUTPUT --print @XMLIn IF @XMLIn <> 0 PRINT @Schema_name+'.'+@table_name FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name END CLOSE tables_cur DEALLOCATE tables_cur RETURN END GO 

Pour “trouver d’où proviennent les données”, vous pouvez lancer SQL Profiler, démarrer votre rapport ou votre application et vous verrez toutes les requêtes émises sur votre firebase database.

Je pense que cela peut être le moyen le plus simple de trouver une chaîne dans toutes les lignes de votre firebase database – sans utiliser les curseurs et 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 

En créant cette procédure stockée, vous pouvez l’exécuter pour n’importe quelle chaîne que vous voulez trouver comme ceci:

 EXEC SPFindAll 'Hello World' 

Le résultat sera comme ceci:

 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 

Ou, vous pouvez utiliser ma requête ici, devrait être plus simple que de devoir créer des sProcs pour chaque DB que vous souhaitez rechercher: 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 NomTable, NomColonne, ColumnValue, ColumnType, COUNT (*) Compte AS FROM #Results GROUP BY NomTable, NomColumn, ColumnValue, ColumnType