SQL: Search all table columns for specific text

I needed to find out which columns within all tables in the database, contained a specific string of text. I therefore wrote the script below ¹ to do exactly that 🙂

CREATE TABLE TempTableColumnResults
( TableName VARCHAR(100),
  ColumnName VARCHAR(100)
)

DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(200)
DECLARE @SQL VARCHAR(1000)
DECLARE @SearchText VARCHAR(100)

SET @SearchText = 'Text to find'

DECLARE TableColumns CURSOR FAST_FORWARD
FOR SELECT
	    O.name AS TableName,
	    C.name AS ColumnName
	FROM SYSCOLUMNS C
	INNER JOIN SYSOBJECTS O
	ON C.ID = O.ID
	WHERE O.xtype IN ('U')
	AND C.xtype NOT IN (34,241,98) --Exclude image, sql_variant, xml data types
	ORDER BY O.name, C.ColOrder

OPEN TableColumns

FETCH NEXT FROM TableColumns
	INTO @TableName, @ColumnName

SET NOCOUNT ON

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'INSERT INTO TempTableColumnResults (TableName, ColumnName) '
	SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @TableName + CHAR(39) + ', '
	SET @SQL = @SQL + CHAR(39) + @ColumnName + CHAR(39) + ' '
	SET @SQL = @SQL + 'FROM ' + QUOTENAME(@TableName) + ' '
	SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE '
	SET @SQL = @SQL + CHAR(39) + '%' + @SearchText + '%' + CHAR(39) + ' '
	EXEC (@SQL)
	FETCH NEXT FROM TableColumns
		INTO @TableName, @ColumnName
END

CLOSE TableColumns
DEALLOCATE TableColumns

SELECT DISTINCT TableName, ColumnName FROM TempTableColumnResults

DROP TABLE TempTableColumnResults