15 Frage: Suchen Sie in allen Tabellen, Zeilen und Spalten einer DB nach einer Zeichenfolge

Frage erstellt am Wed, Apr 22, 2015 12:00 AM

Ich habe mich in einer großen Datenbank verirrt und kann nicht feststellen, woher die Daten stammen, die ich erhalte. Ich habe mich gefragt, ob es mit SQL Server 2005 möglich ist, in allen Tabellen, Zeilen und Spalten einer Datenbank nach einer Zeichenfolge zu suchen?

Hat jemand eine Idee, ob und wie dies möglich ist?

    
76
  1. 2014-05-30 05: 17: 12Z
  2. 2017-03-02 03: 21: 38Z
15 Antworten                              15                         

Dieser Code sollte es in SQL 2005 tun, aber ein paar Vorsichtsmaßnahmen:

  1. Es ist lächerlich langsam. Ich habe es auf einer kleinen Datenbank getestet, die ich nur mit einer Handvoll Tabellen besitze, und es hat viele Minuten gedauert, bis es fertig war. Wenn Ihre Datenbank so groß ist, dass Sie sie nicht verstehen können, ist sie wahrscheinlich ohnehin unbrauchbar.

  2. Ich habe das von der Manschette abgeschrieben. Ich habe keine Fehlerbehandlung vorgenommen und es kann auch zu einer anderen Schlamperei kommen, zumal ich nicht oft Cursor benutze. Ich denke zum Beispiel, dass es eine Möglichkeit gibt, den Spaltencursor zu aktualisieren, anstatt ihn jedes Mal zu schließen /aufzuheben /neu zu erstellen.

Wenn Sie die Datenbank nicht verstehen oder nicht wissen, woher die Daten stammen, sollten Sie wahrscheinlich jemanden finden, der dies tut. Auch wenn Sie feststellen können, wo sich die Daten befinden, werden sie möglicherweise irgendwo dupliziert oder es gibt andere Aspekte der Datenbank, die Sie nicht verstehen. Wenn niemand in Ihrem Unternehmen die Datenbank versteht, sind Sie in einem ziemlich großen Chaos.

 
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
    
90
2017-05-16 12: 20: 09Z
  1. "Wenn niemand in Ihrem Unternehmen die Datenbank versteht, sind Sie in einem ziemlich großen Chaos" - ja, dies ist normalerweise der Fall, wenn die wahrscheinlich neue und einzige IT-Person dies benötigt in etwa so, da das Management keine Mittel für die ordnungsgemäße Durchführung genehmigt. PS. Das hat auf SQL Server 2012 für mich einwandfrei funktioniert.
    2014-08-07 07: 11: 12Z
  2. Was passiert, wenn nicht alle Objekte im Besitz von dbo sind? Wie passe ich es an?
    2014-12-10 17: 48: 05Z
  3. Ich habe das Skript aktualisiert, um Nicht-Dbo-Objekte zu verarbeiten. Ich habe es auch geändert, um die INFORMATION_SCHEMA-Tabellen zu verwenden, was bedeutet, dass es nicht nur mit zukünftigen Versionen verwendbar bleiben sollte, sondern theoretisch auch mit Oracle, MySQL usw. funktionieren sollte. Schließlich wurde es so geändert, dass QUOTENAME für alle Objektnamen verwendet wird, um die Dinge zu handhaben wie Leerzeichen in Tabellennamen.
    2016-03-08 13: 45: 57Z
  4. Es ist nicht ungewöhnlich, mit einer Datenbank zu arbeiten, in der niemand in Ihrem Unternehmen das Schema kennt. Ich werde oft gebeten, Datenbanken nach Software abzufragen, die unser Unternehmen gekauft hat. Wenn der Anbieter uns Unterstützung in Rechnung stellt, ist es sinnvoll zu prüfen, ob Sie das Schema ohne dessen Hilfe herausfinden können.
    2016-09-02 16: 41: 18Z
  5. Sehr hilfreich. Ich musste jedoch die Bedingung COLLATION_NAME IS NOT NULL entfernen, als ich nach UniqueIdentifiers
    suchte
    2017-04-28 18: 10: 43Z

Ich würde vorschlagen, dass Sie sich ein Drittanbieter-Tool dafür suchen, z. B. ApexSQL Search (wahrscheinlich gibt es noch andere) auch da, aber ich benutze dieses, weil es kostenlos ist).

Wenn Sie wirklich den SQL-Weg gehen möchten, können Sie die gespeicherte Prozedur verwenden, die von erstellt wurde Sorna Kumar Muthuraj - Der kopierte Code ist unten. Führen Sie diese gespeicherte Prozedur einfach 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
    
35
2013-03-05 14: 16: 22Z
  1. Diese gespeicherte Prozedur ist weitaus effizienter als einige der anderen Vorschläge, da sie alle Spalten einer Zeile in einer select-Anweisung überprüft, aber nicht unbedingt angibt, welche Zelle Sie verwenden müssen Die Spalte stimmte überein. Es wird auch nicht vorausgesetzt, dass Sie eine vollständige% wildcard% -Suche wünschen. Es werden auch keine temporären Tabellen erstellt, was problematisch ist, wenn es nicht vor dem Ausführen des Codes überprüft wird. Wenn Sie die Datenbank, in der Sie suchen, nicht ändern möchten, konvertieren Sie das Ganze einfach in einen BEGIN /DECLARE /END-Block und bewahren Sie ihn an einem sicheren Ort auf.
    2014-04-06 12: 23: 04Z
  2. Noch ein Vorschlag an die Benutzer: Wenn die Ergebnismenge besonders groß ist und Sie nur "anfangen" möchten, um zu sehen, wo sich die Dinge befinden, ändern Sie die obigen Angaben in "TOP 100" -Limits einschließen oder so.
    2014-04-06 12: 33: 06Z
  3. Ich nehme meine Worte zurück und erstelle keine temporären Tabellen. Es ist sogar ein bisschen schlimmer! Eine Tabelle "SearchTMP" wird erstellt.
    2014-04-06 13: 01: 16Z

Obwohl die zuvor vorgestellten Lösungen gültig und funktionsfähig sind, biete ich demütig einen Code an, der sauberer, eleganter und leistungsfähiger ist, zumindest so, wie ich es sehe.

Zunächst mag man sich fragen: Warum braucht jemand jemals einen Codeausschnitt, um global und blind nach einer Zeichenfolge zu suchen? Hey, sie haben schon Volltext erfunden, weißt du nicht?

Meine Antwort: Ich arbeite hauptsächlich bei Projekten zur Systemintegration. Wenn ich eine neue und nicht komprimierte Datenbank lerne, ist es wichtig, zu ermitteln, wo die Daten gespeichert sind.

Außerdem ist der Code, den ich vorstelle, eine abgespeckte Version eines leistungsstärkeren und gefährlicheren Skripts, mit dem Text in der gesamten Datenbank gesucht und ersetzt wird.

 
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
    
27
2012-09-23 23: 29: 03Z
  1. Dies hat in SQL Server 2012 hervorragend funktioniert. Die Ergebnisse wurden in wenigen Minuten zurückgegeben.
    22.09.2015 10: 52: 25Z
  2. @ Marcus Vinicius Pompeu Ist es möglich, mit derselben Abfrage nach 2 Zeichenfolgen zu suchen?
    2016-06-02 14: 55: 38Z
  3. Ach egal, Marcus, die beiden Zeichenfolgen, nach denen ich suche, sind bis auf das letzte Zeichen genau gleich. Ich werde nur das letzte Zeichen abschneiden. Tolle Antwort übrigens. +1
    2016-06-02 15: 02: 06Z
  4. @ EricS, du hast mich herausgefordert! In zwei oder drei Tagen präsentiere ich uns eine noch bessere Lösung. Grüße.
    2016-06-05 23: 40: 40Z
  5. @ MarcusViniciusPompeu haha, vielen Dank und viel Glück. Ich werde dafür offen bleiben.
    2016-06-06 13: 36: 00Z

Wenn Sie Daten abrufen fFür eine Anwendung ist es sinnvoll, den Profiler zu verwenden und die Datenbank zu profilieren, während die Anwendung ausgeführt wird. Verfolgen Sie es und durchsuchen Sie dann die Ergebnisse nach dieser Zeichenfolge.

    
13
2009-02-26 19: 29: 16Z
  1. Hier haben Sie einen Punkt!
    2014-02-12 05: 09: 13Z

Das SSMS Tools PACK-Add-In (Add-On) für Microsoft SQL Server Management Studio und Microsoft SQL Server Management Studio Express bietet genau das, was Sie benötigen. Bei größeren Datenbanken dauert die Suche einige Zeit, aber das ist zu erwarten. Es enthält auch eine Menge cooler Features, die in erster Linie in SQL Server Management Studio enthalten sein sollten. Probieren Sie es aus www.ssmstoolspack.com/

Zum Ausführen der Tools muss SP2 für SQL Server Management Studio installiert sein.

    
8
2009-07-02 23: 06: 55Z

Ich habe ein Skript angepasst, das 2002 ursprünglich von Narayana Vyas Kondreddi geschrieben wurde. Ich habe die where-Klausel geändert, um auch Text /nText-Felder zu prüfen, indem ich patindex anstelle von like verwendet habe. Ich habe auch die Ergebnistabelle leicht verändert. Aus unvernünftigen Gründen habe ich die Variablennamen geändert und nach Belieben ausgerichtet (keine Respektlosigkeit gegenüber Herrn Kondretti). Der Benutzer möchte möglicherweise die gesuchten Datentypen ändern. Ich habe eine globale Tabelle verwendet, um die Abfrage während der Verarbeitung zu ermöglichen, aber eine permanente Tabelle ist möglicherweise eine intelligentere Methode.

 
/* 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
;
    
6
2009-05-19 21: 27: 35Z

Andere Antworten, die bereits gepostet wurden, funktionieren möglicherweise genauso gut oder besser, aber ich habe sie nicht verwendet. Das folgende SQL, das ich verwendet habe, hat mir wirklich geholfen, als ich versuchte, ein großes System mit einer riesigen (und sehr unorganisierten) SQL Server-Datenbank rückzuentwickeln.

Dies ist nicht mein Code. Ich wünschte, ich könnte den Originalautor gutschreiben, 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
    
4
21.06.2010 21: 55: 47Z
  1. 21.06.2010 22: 04: 38Z

Eigentlich bin ich mit MikeW (+1) einverstanden, es ist besser, den 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 + dynamisches 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 eine 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
    
4
2017-04-07 16: 32: 55Z

@NLwino, sehr gute Abfrage mit einigen Fehlern bei der Keyword-Verwendung. Ich musste es ein wenig modifizieren, um die Schlüsselwörter mit [] zu umbrechen und auch die Spalten char und ntext zu betrachten.

 
    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 mit einer 2,5-GB-Datenbank ausgeführt und es kam in 51 Sekunden zurück.

    
3
2013-08-08 17: 11: 20Z
  1. Wenn dies eine Bearbeitung von @ NLwinos Beitrag ist, sollten Sie seinen Beitrag bearbeiten (wenn Sie einen ausreichenden Ruf haben), anstatt eine neue Antwort zu veröffentlichen.
    2013-08-08 17: 11: 57Z

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

Beachten Sie auch, dass dies LIKE verwendet. Denn das war genau das, was ich brauchte. Es funktioniert für alle Schemas, alle Tabellen und nur für Abfragespalten, die NVARCHAR oder VARCHAR lauten, selbst 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 enthält die Tabelle, die Spalte und den Wert. Die Ausführungszeit für eine kleine Datenbank betrug ~ 3 Sekunden und ergab ca. 3000 Ergebnisse.

    
3
2015-06-19 14: 30: 41Z
  1. Wenn ich dies ausführe, wird der folgende Fehler angezeigt: Falsche Syntax in der Nähe des Schlüsselworts 'table'.
    2013-07-02 10: 38: 25Z
  2. @ KarlGlennon Ich denke, Sie müssen AS table in AS [table] und so weiter ändern.
    2015-04-22 05: 01: 09Z
  3. Hinweis: Die Ergebnisse dieser Abfrage enthalten Ansichten und Tabellen;).
    2015-04-22 05: 02: 08Z
 
/*
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:      <Joshy,,Name>
-- Create date: <Create Date,,>
-- Description: <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
    
2
2015-04-22 05: 19: 19Z

Um herauszufinden, woher die Daten stammen, können Sie SQL Profiler starten, Ihren Bericht oder Ihre Anwendung starten und alle Abfragen sehen, die für Ihre Datenbank ausgegeben wurden.

    
1
2009-07-03 03: 29: 34Z

Ich denke, dies ist der einfachste Weg, eine Zeichenfolge in allen Zeilen Ihrer Datenbank zu finden, ohne die Cursor und FOR XML zu verwenden.

 
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 eine beliebige Zeichenfolge ausführen, die Sie folgendermaßen suchen möchten:

 
EXEC SPFindAll 'Hello World'

Das Ergebnis sieht folgendermaßen aus:

 
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
    
1
2015-04-22 05: 20: 24Z

Oder Sie können meine Abfrage hier verwenden. Sollte einfacher sein, als sProcs für jede zu durchsuchende Datenbank 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 TableName, ColumnName, ColumnValue, ColumnType

    
0
2017-03-02 03: 19: 57Z

Diese Abfrage kann die Sache für Sie erledigen.

 
DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239, 99)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @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_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur
    
0
2019-03-01 17: 28: 17Z
Quelle platziert Hier