Mar 252009
 

I’ve many times needed to find a value within an entire database.

Here’s a stored procedure that accomplishes that.

CREATE PROCEDURE [dbo].[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