MSSQL - Find A String/Value in Any Table in A Database

CREATE PROCEDURE [dbo].[FindMatchingValueInAnyTable]

(

    @Value VARCHAR(64)

)

AS

BEGIN

    DECLARE @sql VARCHAR(MAX), @TableName VARCHAR(64), @ColumnName VARCHAR(64)

    CREATE TABLE #Results (TableName VARCHAR(64), ColumnName VARCHAR(64))

    DECLARE TABLES CURSOR FOR SELECT

                                   o.name

                                  ,c.name

                              FROM syscolumns c

                              INNER JOIN sysobjects o

                                ON c.id = o.id

                              WHERE

                                   o.type = 'U'

                               AND c.xtype IN (167, 175, 231, 239)

                              ORDER BY

                                   o.name

                                  ,c.name

    OPEN TABLES FETCH NEXT FROM TABLES INTO @TableName, @ColumnName

    WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @TableName + '] '

        SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @ColumnName + '])) LIKE ''%' + @value + '%'') '

        SET @sql = @sql + 'INSERT INTO #Results ( TableName, ColumnName ) VALUES (''' + @TableName + ''', '''

        SET @sql = @sql + @ColumnName + ''')'

        EXEC(@sql)

        FETCH NEXT FROM TABLES INTO @TableName, @ColumnName

    END

    CLOSE TABLES

    DEALLOCATE TABLES

   

    SELECT * FROM #Results DROP TABLE #Results

END

Read more: http://blog.biztalk-info.com/archive/2009/03/24/Find_a_value_in_any_table_in_a_database.aspx#ixzz0iLLlxSnz