Sucht in allen Tabellen, Zeilen und Spalten eines DB nach einem String

Ich bin in einer großen database verloren und ich bin nicht in der Lage zu finden, woher die Daten stammen. Ich frage mich, ob es mit SQL Server 2005 möglich ist, in allen Tabellen, Zeilen und Spalten einer database nach einer Zeichenfolge zu suchen?

Hat jemand eine Idee, wenn es möglich ist und wie?

   

Dieser Code sollte es in SQL 2005 tun, aber ein paar Vorbehalte:

  1. Es ist unglaublich langsam. Ich testete es auf einer kleinen database, die ich mit nur einer Handvoll Tabellen habe, und es dauerte viele Minuten, um abzuschließen. Wenn Ihre database so groß ist, dass Sie sie nicht verstehen können, wird sie wahrscheinlich sowieso unbrauchbar sein.

  2. Ich habe das aus dem Stegreif geschrieben. Ich habe keine Fehlerbehandlung vorgenommen und es könnte eine andere Schlamperei geben, besonders da ich oft keine Cursor verwende. Ich denke zum Beispiel, dass es eine Möglichkeit gibt, den Spaltencursor zu aktualisieren, anstatt ihn jedes Mal zu schließen / freizugeben.

Wenn du die database nicht verstehen kannst oder nicht weißt, woher das Zeug kommt, solltest du wahrscheinlich jemanden finden, der das tut. Selbst wenn Sie herausfinden können, wo sich die Daten befinden, können sie irgendwo dupliziert werden oder es gibt andere Aspekte der database, die Sie nicht verstehen. Wenn niemand in Ihrer Firma die database versteht, dann sind Sie in einem ziemlich großen Durcheinander.

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 

Ich schlage vor, dass Sie ein Tool von Drittanbietern dafür finden, wie ApexSQL Search (es gibt wahrscheinlich auch andere da draußen, aber ich benutze dieses, weil es kostenlos ist).

Wenn Sie wirklich den SQL-Weg gehen wollen, können Sie versuchen, die gespeicherte Prozedur zu verwenden, die von Sorna Kumar Muthuraj erstellt wurde – kopierter Code ist unten. Führen Sie einfach diese gespeicherte Prozedur für alle Tabellen in Ihrem Schema aus (einfach mit 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 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 

Obwohl die vorgestellten Lösungen gültig sind und funktionieren, biete ich demütig einen Code an, der sauberer, eleganter und mit besserer performance ist, zumindest so, wie ich es sehe.

Erstens könnte man fragen: Warum sollte jemand jemals ein Code-Snippet benötigen, um global und blind nach einer Zeichenkette zu suchen? Hey, sie haben den Volltext schon erfunden, weißt du nicht?

Meine Antwort: Meine hauptsächliche Arbeit ist bei Systemintegrationsprojekten, und es ist wichtig, zu erfahren, wo die Daten geschrieben sind, wenn ich eine neue und undokumentierte database lerne, was selten passiert.

Außerdem ist der Code, den ich vorstelle, eine abgespeckte Version eines leistungsfähigeren und gefährlicheren Skripts, das Text in der gesamten database sucht und ersetzt.

 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 

Wenn Sie Daten von einer Anwendung “bekommen”, ist es sinnvoll, den Profiler zu verwenden und die database während der Ausführung der Anwendung zu profilieren. Verfolgen Sie es und suchen Sie dann nach den Ergebnissen für diese Zeichenfolge.

Das SSMS Tools PACK Add-In (Add-On) für Microsoft SQL Server Management Studio und Microsoft SQL Server Management Studio Express wird genau das tun, was Sie brauchen. Bei einer größeren database braucht es einige Zeit, aber das ist zu erwarten. Es enthält auch eine Menge cooler functionen, die eigentlich in SQL Server Management Studio enthalten sein sollten. Probieren Sie es aus http://www.ssmstoolspack.com/

Sie müssen SP2 für SQL Server Management Studio installiert haben, um die Tools auszuführen.

Ich habe 2002 ein von Narayana Vyas Kondreddi geschriebenes Skript adaptiert. Ich änderte die where-Klausel, um auch text / ntext-Felder zu überprüfen, indem ich patindex anstelle von like verwendete. Ich habe auch die Ergebnistabelle leicht verändert. Unnötigerweise habe ich die Variablennamen geändert und so ausgerichtet, wie ich es vorziehe (keine Missachtung von Mr. Kondretti). Der Benutzer möchte möglicherweise die gesuchten Datentypen ändern. Ich habe eine globale Tabelle verwendet, um Abfragen in der Mitte der Verarbeitung zu ermöglichen, aber eine permanente Tabelle könnte ein klügerer Weg sein.

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

Andere bereits gepostete Antworten mögen gleich gut oder besser funktionieren, aber ich habe sie nicht benutzt. Allerdings habe ich das folgende SQL verwendet, und es hat mir wirklich geholfen, als ich versuchte, ein großes System mit einer riesigen (und sehr unorganisierten) SQL Server-database rückzuentwickeln.

Das ist nicht mein Code. Ich wünschte, ich könnte dem ursprünglichen Autor danken, aber ich kann den Link zum Artikel nicht mehr finden 🙁

 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 

Eigentlich stimme ich MikeW (+1) zu, es ist besser, Profiler für diesen Fall zu verwenden.

Wie auch immer, wenn Sie wirklich alle (n) varchar-Spalten in db erfassen und eine Suche durchführen müssen. Siehe unten. Ich nehme an, INFORMATION_SCHEMA.Tables + dynamic SQL zu verwenden. Die einfache Suche:

 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 

und eins mit LIKE-Klausel:

 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, sehr gute Abfrage mit ein paar Fehlern für die Verwendung von Schlüsselwörtern. Ich musste es etwas modifizieren, um die Schlüsselwörter mit [] zu umhüllen und auch die Spalten char und ntext zu sehen.

  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) 

Ich habe es auf 2,5 GB database ausgeführt und es kam in 51 Sekunden zurück

Dies verwendet keine Cursor oder ähnliches, nur eine dynamische Abfrage.

Beachten Sie auch, dass dies LIKE . Da war das, was ich brauchte. Es funktioniert für alle Schemas, alle Tabellen und nur die Spalten, die NVARCHAR oder VARCHAR auch wenn sie UDDT haben.

 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) 

Die Ausgabe gibt Ihnen die Tabelle, die Spalte und den Wert. Die Zeit für die Ausführung in einer kleinen database betrug ~ 3 Sekunden und hatte ungefähr 3000 Ergebnisse.

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

Um “herauszufinden, woher die Daten stammen, aus denen ich komme”, können Sie SQL Profiler starten, Ihren Bericht oder Ihre Anwendung starten und alle Abfragen, die für Ihre database ausgegeben werden, sehen.

Ich denke, dass dies eine einfache Möglichkeit sein kann, eine Zeichenfolge in allen Zeilen Ihrer database zu finden – ohne Verwendung von Cursorn und 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 

Wenn Sie diese gespeicherte Prozedur erstellen, können Sie sie für jede Zeichenfolge ausführen, die Sie folgendermaßen suchen möchten:

 EXEC SPFindAll 'Hello World' 

Das Ergebnis wird wie folgt aussehen:

 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 

Oder Sie können meine Abfrage hier verwenden, sollte einfacher als sProcs für jeden DB, den Sie suchen möchten, zu erstellen: 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 Tabellenname, Spaltenname, Spaltenwert, Spaltentyp, COUNT (*) AS Count FROM #Results GROUP BY Tabellenname, Spaltenname, Spaltenwert, Spaltentyp