/*
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'
