Almost a year ago, I had reason to write a SQL script that would search for text anywhere in a database.
Since then, I’ve had reason to modify and enhance it. The modification was done primarily to support SQL Server 2000, which I still have to support.
The enhancement was done primarily to support user-defined data types. Also, I added some error-handling.
In order to allow for up to 8000 characters in a variable on SQL 2000, I changed the NVARCHARs to VARCHAR, and MAX to 8000 in the variable declarations. If you want to use this script on SQL 2005 or newer, you can change these back to make the script more scalable.
UPDATE: Revised script for SQL 2008 and newer versions
-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
-- Set @PrintAllSQLStmts to 1 if you want all of the statements printed as messages
DECLARE @TestValue VARCHAR(8000)
DECLARE @SearchViews BIT
DECLARE @PrintAllSQLStmts BIT
SET @PrintAllSQLStmts = 0
SET @SearchViews = 0
SET @TestValue = 'text to search'
------------------------------------------------------------------------------------------------------------------------------------
/* If the version of SQL Server is 2005 or newer, these can be set to MAX rather than 8000 */
DECLARE @SQL VARCHAR(8000)
DECLARE @varSQL VARCHAR(8000)
DECLARE @WhereClause VARCHAR(8000)
----------------------------------------------------------------------------------------------
DECLARE @TableName VARCHAR(128)
DECLARE @varTableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @MaxLength VARCHAR(4)
DECLARE @NMaxLength VARCHAR(4)
IF @@VERSION LIKE 'Microsoft SQL Server 2000%'
BEGIN
SET @MaxLength = '8000'
SET @NMaxLength = '4000'
END
ELSE
BEGIN
SET @MaxLength = 'MAX'
SET @NMaxLength = 'MAX'
END
DECLARE @SchemaObjects TABLE (
TABLE_NAME VARCHAR(128),
TABLE_SCHEMA VARCHAR(128)
)
DECLARE @TypeTable TABLE (
UserType VARCHAR(128) PRIMARY KEY,
BaseType VARCHAR(128)
)
DECLARE @SizableTypes TABLE (
TypeName VARCHAR(128) PRIMARY KEY
)
INSERT INTO @SizableTypes VALUES ('VARBINARY')
INSERT INTO @SizableTypes VALUES ('VARCHAR')
INSERT INTO @SizableTypes VALUES ('NVARCHAR')
INSERT INTO @SizableTypes VALUES ('CHAR')
INSERT INTO @SizableTypes VALUES ('NCHAR')
INSERT INTO @TypeTable
SELECT ut.name, bt.name FROM SysTypes ut, SysTypes bt
WHERE ut.xusertype > 256 AND bt.xusertype < 256
AND ut.xtype = bt.xtype
INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
IF @SearchViews = 1
BEGIN
INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
SET @WhereClause = ''
DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
SET @WhereClause = @WhereClause + '(CONVERT(VARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
FETCH NEXT FROM ColumnCursor INTO @ColumnName
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
IF LEN(@WhereClause) > 0
BEGIN
SET @varTableName = REPLACE(@TableName,' ','_')
SET @varSQL = 'DECLARE @' + @varTableName + ' TABLE (' + CHAR(10)
SELECT @varSQL = @varSQL + ' [' + sc.name + '] ' +
CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME'
WHEN tt.UserType IS NOT NULL THEN UPPER(tt.BaseType)
ELSE UPPER(st.name) END +
CASE WHEN tt.BaseType IN (SELECT TypeName FROM @SizableTypes) OR st.name IN (SELECT TypeName FROM @SizableTypes) THEN '(' +
CASE WHEN (LEFT(tt.BaseType,3) = 'VAR' OR LEFT(st.name,3) = 'VAR') AND sc.[length] > 8000 THEN @MaxLength
WHEN (LEFT(tt.BaseType,3) = 'NVA' OR LEFT(st.name,3) = 'NVA') AND sc.[length] > 4000 THEN @NMaxLength
ELSE CAST(sc.[length] AS VARCHAR) END + ') '
ELSE ' ' END +
CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
FROM SysObjects so
JOIN SysColumns sc ON sc.id = so.id
JOIN SysTypes st ON st.xusertype = sc.xusertype
LEFT OUTER JOIN @TypeTable tt ON st.name = tt.UserType
WHERE so.name = @TableName
ORDER BY sc.colid
SET @SQL = CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause + ';'
SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)
SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10)
SET @varSQL = @varSQL + 'BEGIN' + CHAR(10)
SET @varSQL = @varSQL + ' SET NOCOUNT OFF;' + CHAR(10)
IF @PrintAllSQLStmts = 0
SET @varSQL = @varSQL + ' PRINT ''' + CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + CHAR(10) + REPLACE(@SQL,'''','''''') + ''';' + CHAR(10)
ELSE
PRINT CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + @SQL + CHAR(10)
SET @varSQL = @varSQL + ' SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';' + CHAR(10)
SET @varSQL = @varSQL + ' SET NOCOUNT ON;' + CHAR(10)
SET @varSQL = @varSQL + 'END' + CHAR(10)
SET @varSQL = @varSQL + CHAR(10) + CHAR(10) + CHAR(10)
--PRINT @varSQL
EXEC (@varSQL)
IF @@ERROR <> 0
BEGIN
PRINT '/* Statement causing error */' + CHAR(10) + CHAR(10) + @varSQL + CHAR(10) + '/* End Statement */' + CHAR(10)
PRINT 'Length of SQL statement: ' + CONVERT(VARCHAR,LEN(@varSQL)) + CHAR(10)
END
END
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor