Search All Columns in All Tables for a string

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFindTextInColumns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFindTextInColumns]
GO

CREATE PROCEDURE spFindTextInColumns (@TableName sysname = NULL,
	@StringToLookFor varchar(500))
AS
-- 	Example Calls
-- 	EXECUTE spFindTextInColumns MyTable, 'tcart' --search a specific table
-- 		
-- 	EXECUTE spFindTextInColumns default, 'tcart' --search all tables
-- 	GO

	DECLARE @columnName nvarchar(128),
		@dateType nvarchar(128),
		@row smallint,
		@rowCount int,
		@sql nvarchar(1000)

	DECLARE string_find_cursor CURSOR FAST_FORWARD FOR 
		SELECT [name]
		FROM SYSOBJECTS 
		WHERE (
				OBJECTPROPERTY(id, N'IsMsShipped') = 0
				AND OBJECTPROPERTY(id, N'IsUserTable') = 1
			) AND (@TableName IS NULL OR [name] = @TableName)
		ORDER BY [name]
	
	OPEN string_find_cursor
	
	FETCH NEXT FROM string_find_cursor 
	INTO @tableName
	
	SET @StringToLookFor = '%' + @StringToLookFor + '%'
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @row = 1
	
		SELECT @rowCount = MAX([ORDINAL_POSITION])
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @tableName 
		GROUP BY [ORDINAL_POSITION]
	
		WHILE @row <= @rowCount
		BEGIN 
			SELECT @columnName = '[' + [COLUMN_NAME] + ']',
				@dateType = [DATA_TYPE]
			FROM [INFORMATION_SCHEMA].[COLUMNS]
			WHERE [TABLE_NAME] = @tableName 
				AND [ORDINAL_POSITION] = @row
			ORDER BY [ORDINAL_POSITION]
	
			SET @row = @row + 1  
			
			SET @sql = NULL
	
			IF @dateType IN ( N'char', N'varchar', N'text')
				SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', ' + @columnName + ') > 0'
			ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
				SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', CAST(' + @columnName + ' As TEXT)) > 0'
			
			IF @sql IS NOT NULL
			BEGIN
				SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''[' + @tableName + '].' + @columnName + ''''
				--PRINT (@sql)
				EXEC (@sql)
			END
		END
	
		FETCH NEXT FROM string_find_cursor 
		INTO @tableName
	END
	
	CLOSE string_find_cursor
	DEALLOCATE string_find_cursor


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO