Next Web Designs

In working with SQL server 2005, I came across this useful query which will loop through every table and every field in a database searching for specified text.  This can really come in handy when you're debugging someone else's un-documented database and you know the text, but you're not sure where it is stored in the database.  Note that this will store the results of this query in a table called "Results", so if you have a table with that name, you'll want to rename the table below.  Also be sure to change your search criteria...  Enjoy!

DROP TABLE #Results
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('<YOUR_SEARCH_DATA_HERE>','''')
 
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', 'uniqueidentifier', 'smallint', 'identity'
      )
      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