It's sometime useful to search an entire database to find out where some specific data is... for instance, you entered data into the UI but you aren't sure where it got stored.

This post contains a SQL Script to search a database for any specific string in any column in any table in your database.

To use this script, simply edit the first line to set the @value variable to the string you wish to search for (see the comment).  You can include "%" as the standard SQL Wildcard character.  As is, the script will search for any string starting with "C:\" ... but you can use exact strings or any arbitrary string you wish.

I hope you find this useful:

DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)

SET @value = 'C:%' -- *** <-- Set this to the value you're searching for *** --

SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '

DECLARE theTableCursor CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' 
       AND TABLE_NAME NOT LIKE '%temp%' AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
     ORDER BY TABLE_NAME

OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
    DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = @tableName AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
         ORDER BY ORDINAL_POSITION

    OPEN theColumnCursor
    FETCH NEXT FROM theColumnCursor INTO @columnName

    WHILE @@FETCH_STATUS = 0 -- spin through Column entries
    BEGIN
        SET @sql = @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @value + 
                   ''') PRINT ''Value found in Table: ' + @tableName + ', Column: ' +  @columnName + ''''
        EXEC (@sqlPreamble + @sql)
        FETCH NEXT FROM theColumnCursor INTO @columnName
    END
    CLOSE theColumnCursor
    DEALLOCATE theColumnCursor

    FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor

 The output of this script will be the table name, and column name, where the data was found.  You can then do a SELECT on that table to see which row(s) contain the data.  A sample output looks like this:

Value found in Table: Publish_Server_Ref, Column: SourceRootLocation
Value found in Table: PublishServerRef, Column: LocalPublishPath
Value found in Table: System_Params, Column: ShortValue
Value found in Table: System_Variable, Column: Value
Value found in Table: System_Variable, Column: DefaultValue
Value found in Table: SystemConfig, Column: ParameterValue
Value found in Table: Website, Column: PublishRootPath
Value found in Table: Website, Column: ProtectedRootPath