SQL Script to Find Text in all Database Tables and Views…Revised!

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

SQL Server Script to Check for Blocking Queries

Even though SQL Server 2000 is no longer officially supported by Microsoft, quite a few SQL 2K databases still exist in production environments. One that had been giving me trouble for a couple of days was intermittently causing a web app to fail, and the error message indicated that the failure due to either a SQL timeout or an unresponsive server. This problem was not constant, but happened every few minutes.

Several other apps that accessed the same database as the one that was only working some of the time. Each app had its own set of tables, so I figured there must be exclusive locks being set on the tables that the problem app used. To determine the cause of this problem, I had to find out what process was locking the table.

This being SQL 2000, I could not use this script, which works on SQL 2008:

USE master
GO
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

For SQL 2000, try this one:

USE master
GO
EXEC master.dbo.sp_lock
GO
EXEC master.dbo.sp_who2
GO
SELECT req_spid AS 'spid', 
DB_NAME(rsc_dbid) AS 'Database', 
OBJECT_NAME(rsc_objid) AS 'Name', 
rsc_indid AS 'Index', 
rsc_text AS 'Description', 
ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'
	WHEN rsc_type = 2 THEN 'Database' 
	WHEN rsc_type = 3 THEN 'File'
	WHEN rsc_type = 4 THEN 'Index' 
	WHEN rsc_type = 5 THEN 'Table' 
	WHEN rsc_type = 6 THEN 'Page'
	WHEN rsc_type = 7 THEN 'Key'
	WHEN rsc_type = 8 THEN 'Extent'
	WHEN rsc_type = 9 THEN 'RID (Row ID)'
	WHEN rsc_type = 10 THEN 'Application'
	ELSE 'Unknown'
END, 
Status = CASE WHEN req_status = 1 THEN 'Granted' 
	WHEN req_status = 2 THEN 'Converting' 
	WHEN req_status = 3 THEN 'Waiting' 
	ELSE 'Unknown' 
END, 
OwnerType = CASE WHEN req_ownertype = 1 THEN 'Transaction' 
	WHEN req_ownertype = 2 THEN 'Cursor' 
	WHEN req_ownertype = 3 THEN 'Session' 
	WHEN req_ownertype = 4 THEN 'ExSession' 
	ELSE 'Unknown' 
END, 
LockRequestMode = CASE WHEN req_mode = 0 THEN 'No access ' 
	WHEN req_mode = 1 THEN 'Sch-S (Schema stability)' 
	WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'
	WHEN req_mode = 3 THEN 'S (Shared)' 
	WHEN req_mode = 4 THEN 'U (Update)' 
	WHEN req_mode = 5 THEN 'X (Exclusive)' 
	WHEN req_mode = 6 THEN 'IS (Intent Shared)' 
	WHEN req_mode = 7 THEN 'IU (Intent Update)' 
	WHEN req_mode = 8 THEN 'IX (Intent Exclusive)' 
	WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'
	WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'
	WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)' 
	WHEN req_mode = 12 THEN 'BU. (Bulk operations)' 
	WHEN req_mode = 13 THEN 'RangeS_S' 
	WHEN req_mode = 14 THEN 'RangeS_U' 
	WHEN req_mode = 15 THEN 'RangeI_N' 
	WHEN req_mode = 16 THEN 'RangeI_S' 
	WHEN req_mode = 17 THEN 'RangeI_U' 
	WHEN req_mode = 18 THEN 'RangeI_X' 
	WHEN req_mode = 19 THEN 'RangeX_S' 
	WHEN req_mode = 20 THEN 'RangeX_U' 
	WHEN req_mode = 21 THEN 'RangeX_X' 
	ELSE 'Unknown' 
END 
FROM master.dbo.syslockinfo 
GO
SELECT * FROM master.dbo.sysprocesses

This query generates several tables, and the last table is most helpful. The “blocked” column will show how many exclusive locks are being held by the process denoted by the number in the “spid” column.

If you have DBA access, you can run “DBCC INPUTBUFFER(spid)”, substituting the spid number found above, and the text of the troublemaking query will be returned.