TSQL Search tables for string

TSQL-SearchTables.md

The following is an example of how to search tables for a value. It may not be the most efficient but it works

Tested against MS SQL Server 2000-2017

Example

DECLARE @SearchStr nvarchar(100);
SET @SearchStr = 'SEARCH TEXT HERE';

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630));

SET NOCOUNT ON;

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110);
SET  @TableName = '';
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''');

WHILE @TableName IS NOT NULL
BEGIN
  SET @ColumnName = ''
  SET @TableName =
  ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    FROM     INFORMATION_SCHEMA.TABLES
    WHERE
      TABLE_TYPE = 'BASE TABLE'
      AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
      AND OBJECTPROPERTY(
            OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped'
          ) = 0
  );

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
    SET @ColumnName =
    ( SELECT MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2)
        AND TABLE_NAME    = PARSENAME(@TableName, 1)
        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
        AND QUOTENAME(COLUMN_NAME) > @ColumnName
    );

    IF @ColumnName IS NOT NULL
    BEGIN
      INSERT INTO #Results
      EXEC (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
      );
    END;
  END;
END;

-- SELECT ColumnName, ColumnValue FROM #Results
SELECT DISTINCT ColumnName, ColumnValue
FROM #Results;

DROP TABLE #Results;