Find Any Value in Any Database Column in Any Table

I found an interesting bit of code today from this author...

http://wellknownmeats.com/ms-sql-server-find-a-value-in-any-field-in-any-table/

This author has come up with an excellent way of scanning a database [Every Table, Every Column] for any Value. As I'm sure you can imagine, this is incredibly useful for a developer, especially those who have to support existing applications...

I've wrapped this into a stored procedure, and later on I made other pertinent, valuable information to this query.

This can be of great power if directly given to the user--and possibly of great destruction. Remember, not everyone should be priveleged to all information.

Now, for those ambitious persons out there, this information can be augmented with additional useful information--Perhaps the value of the Primary Key, or a SQL Statement to query that table/column/value, etc. Good luck!

CREATE PROCEDURE [Developer].[FindMatchingValueInAnyTable]

(

    @Value VARCHAR(64)

)

AS

BEGIN

 

    DECLARE @sql VARCHAR(MAX)

    DECLARE @TableName VARCHAR(64)

    DECLARE @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