Suche von Text in Daten aller Tabellen / Search text in data of all tables

Das nachfolgende T-SQL-Script erlaubt das Suchen eines Texts in allen Tabellen einer Datenbank. Nachfolgend ein Beispiel-Ergebnis für den Suchtext "Stehleuchte" in einer Standard Sage 100 Demo-Datenbank.

Die Ausführung des SQL-Scripts kann folglich - je nach Umfang des Datenbestands - einige Zeit dauern.

Das SQL-Script ist sehr gut dazu geeignet, herauszufinden, in welchen SQL-Tabellen und -Feldern bestimmte Informationen vorkommen.

Zu beachten ist natürlich, dass je nach eingesetzter Anwendung, die die Daten in die Datenbank schreibt, unter Umständen einige Informationen nicht im Klartext in der Datenbank stehen, sondern beispielsweise binär serialisiert oder dergleichen. Derartige Fundstellen werden natürlich nicht in der Ergebnisliste erscheinen!

Das Script berücksichtigt außerdem einige Besonderheiten, die beispielsweise im Rahmen der Sage ERP vom Software-Hersteller "Sage" verwendet werden.

Dazu gehören Konstrukte in den Daten wie:

→ Trennung der Werte mit Semikolon.

→ Verwendung Parameter-Definition mit ":="

Im SQL-Script finden sich einige Stellen, die mit "TODO" markiert sind.

Diese sollten geprüft werden!

The following T-SQL script allows finding a text in all tables of a database. See following example result for the search text "Stehleuchte" within a default Sage 100 demo database.

Depending on the volume of data, the execution of the SQL script can therefore take some time.

The SQL script is well suited to finding out which SQL tables and fields certain information occurs.

Of course, it should be noted that depending on the application used that writes the data to the database, some information may not be in plain text in the database but rather, for example, in binary serialization or something similar. Of course, such locations will not appear in the list of results!

The script also takes into account some special features that are used, for example, by the software manufacturer "Sage" as part of the Sage ERP.

This includes constructs in the data such as:

→ Separation of the values with a semicolon.

→ Usage of parameter definitions with ":="

A few places in the SQL script are marked with "TODO".

These should be checked.

IF NOT EXISTS 
( SELECT * 
  FROM dbo.sysobjects 
  WHERE id = OBJECT_ID(N'[dbo].[tTempSearchResults]') 
  AND OBJECTPROPERTY(id , N'IsUserTable') = 1 
)
BEGIN
    
  CREATE TABLE [dbo].[tTempSearchResults]
    (
        [ID]          int IDENTITY(1,1) 
      , [TableName]   varchar(250) NOT NULL 
      , [ColumnName]  varchar(250) NOT NULL 
    )
  
  ALTER TABLE [dbo].[tTempSearchResults] ADD CONSTRAINT [PK_tTempSearchResults] 
    PRIMARY KEY CLUSTERED 
  (
    [ID]
  )  
  ON [PRIMARY] 

END

GO

DECLARE @sSearchStr varchar(1024) 

-- TODO: Set search string here
SET @sSearchStr = 'Stehleuchte'

-- Build search variants
DECLARE @sSearchStrV1 varchar(1024)
DECLARE @sSearchStrV2 varchar(1024)
DECLARE @sSearchStrV3 varchar(1024)
DECLARE @sSearchStrV4 varchar(1024)
DECLARE @sSearchStrV5 varchar(1024)

-- Exact match
SET @sSearchStrV1 = QUOTENAME(@sSearchStr , '''')

-- Consider special field values for ERP separated parameters

-- Example if XZY is searched: '...BLA;XYZ;ANO...'
SET @sSearchStrV2 = QUOTENAME('%;' + @sSearchStr + ';%' , '''')

-- Example if XZY is searched: '...BLA;XYZ'
SET @sSearchStrV3 = QUOTENAME('%;' + @sSearchStr , '''')

-- Example if XZY is searched: 'XYZ;BLA...'
SET @sSearchStrV4 = QUOTENAME( @sSearchStr + ';%' , '''')

-- Example if XZY is searched: 'Param:=XYZ'
SET @sSearchStrV5 = QUOTENAME('%:=' + @sSearchStr + '%' , '''')

TRUNCATE TABLE [dbo].[tTempSearchResults]

DECLARE @sTableName varchar(256) 
DECLARE @sColumnName varchar(128) 

SET @sTableName  = N''

WHILE @sTableName IS NOT NULL
BEGIN
    
  SET @sColumnName = N''

  SET @sTableName  = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                       FROM INFORMATION_SCHEMA.TABLES
                       WHERE TABLE_TYPE = 'BASE TABLE'
                       AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @sTableName
                       AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) , 'IsMSShipped') = 0
                       -- TODO: Set optional table name filters here
                       -- Some default tables, also for additional programmings are already set.
                       -- You should always analyze the database first to make sure you do not(!) miss important tables.
                       -- AND ( TABLE_NAME LIKE '%MyTest1%' OR TABLE_NAME = 'MyTest2Table' )
                     )

  WHILE @sTableName IS NOT NULL 
  AND @sColumnName IS NOT NULL 
  BEGIN

    SET @sColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME))
                          FROM INFORMATION_SCHEMA.COLUMNS
                          WHERE TABLE_SCHEMA = PARSENAME(@sTableName , 2)
                          AND TABLE_NAME     = PARSENAME(@sTableName , 1)
                          AND ( DATA_TYPE = 'varchar' or DATA_TYPE = 'nvarchar' ) 
                          AND QUOTENAME(COLUMN_NAME) > @sColumnName
                          -- TODO: Optional: Filter columns, if sensible
                          AND COLUMN_NAME NOT IN 
                          (
                            'XXX', 'YYY'
                          ) 
                        )

    IF @sColumnName IS NOT NULL
    BEGIN

      DECLARE @sTableNameNoBrackets VARCHAR(250)

      SET @sTableNameNoBrackets = PARSENAME(@sTableName , 1)

      DECLARE @sColumnNameNoBrackets VARCHAR(250)

      SET @sColumnNameNoBrackets = PARSENAME(@sColumnName , 1)

      INSERT INTO [dbo].[tTempSearchResults]
      EXEC (
        'SELECT  TOP 1 ''' + @sTableNameNoBrackets + ''',''' + @sColumnNameNoBrackets + ''' FROM ' + @sTableName + ' WITH (READUNCOMMITTED) ' 
        + ' WHERE ( ' + @sColumnName + ' = ' + @sSearchStrV1 + ' ' 
        + ' OR ' + @sColumnName + ' LIKE ' + @sSearchStrV2 + ' ' 
        + ' OR ' + @sColumnName + ' LIKE ' + @sSearchStrV3 + ' ' 
        + ' OR ' + @sColumnName + ' LIKE ' + @sSearchStrV4 + ' ' 
        + ' OR ' + @sColumnName + ' LIKE ' + @sSearchStrV5 + ' ' 
        + ' ) ' 
      )

    END
        
  END

END

GO

-- Show final results
SELECT [TableName] , [ColumnName] 
FROM [dbo].[tTempSearchResults] WITH(Readuncommitted)
GROUP BY [TableName] , [ColumnName]

GO