LearnItFirst User Forum

/*
    Author: Scott Whigham FROM http://www.LearnSqlServer.com/

    Description: Queries every string-based column (VARCHAR/NVARCHAR/CHAR/NCHAR only) to see if the column contains the @SearchTerm

    Versions: SQL Server 2000+

    Creation Date: June 5, 2007

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
CREATE PROC SearchForTermInAllStringColumnsInDatabase (
    @SearchTerm VARCHAR(1024)
    , @Collation SYSNAME = 'SQL_Latin1_General_CP1_CI_AS' -- Only here so that we can decide if the caller wants a case or language-sensitive search
) 
AS
DECLARE @ColumnsToSearch TABLE (
    LineId INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , SchemaName SYSNAME NOT NULL
    , TableName SYSNAME NOT NULL
    , ColumnName SYSNAME NOT NULL
)
SET NOCOUNT ON

-- Step 1: What is the LEN(@SearchTerm)?
DECLARE @LengthOfSearchTerm INT
SET @LengthOfSearchTerm = LEN(@SearchTerm)

-- Step 2: Find all string-based columns with a column length > @LengthOfSearchTerm
INSERT @ColumnsToSearch (SchemaName, TableName, ColumnName)
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.TABLES t
    ON c.TABLE_SCHEMA=t.TABLE_SCHEMA
    AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
    AND c.DATA_TYPE IN ('varchar', 'nvarchar','char', 'nchar')
    AND c.CHARACTER_MAXIMUM_LENGTH >= @LengthOfSearchTerm

-- Search every column!
DECLARE @LineId INT, @FullyQualifiedTableName NVARCHAR(256), @ColumnToSearch SYSNAME, @Sql NVARCHAR(1024)

SELECT @LineId = LineId, @FullyQualifiedTableName = '[' + SchemaName + '].[' + TableName + ']', @ColumnToSearch = '[' + ColumnName + ']' FROM @ColumnsToSearch

WHILE @LineId IS NOT NULL
    BEGIN
        -- Create the base query against the table
  SET @Sql = 'SELECT ''' + @FullyQualifiedTableName + '.' + @ColumnToSearch + ''' AS [Table_Column], * FROM '
        + @FullyQualifiedTableName + ' WHERE ' + @ColumnToSearch + ' LIKE ''%' + @SearchTerm + '%'' COLLATE ' + @Collation

        -- Only return rows that actually contain the search term otherwise write out a message saying the table/column did not have the criteria
        EXEC ( 'IF NOT EXISTS (' + @Sql + ') PRINT ''' + @FullyQualifiedTableName + '.' + @ColumnToSearch + ' - no matches'' ELSE ' + @Sql)

        DELETE @ColumnsToSearch WHERE LineId=@LineId
        SET @LineId = NULL
        SELECT @LineId = LineId, @FullyQualifiedTableName = '[' + SchemaName + '].[' + TableName + ']', @ColumnToSearch = '[' + ColumnName + ']' FROM @ColumnsToSearch
    END
GO
EXEC SearchForTermInAllStringColumnsInDatabase 'sevrer'