Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find Text in all columns of all tables in a Database

By Joshua A. Walker, 2013/12/20

Change the 'DBAdmin' text value for @TEXT to whatever text you are looking for in your database. Run the code and it will tell you where that text appears. It will list Table Name, Column Name and the number of occurances.

In my example below the word "DBAdmin" was found in 5 tables, all with column name "DBName"

/*****************************************************************************************
AUTHOR:        JOSHUA A WALKER
DATE:        20070125
PURPOSE:    Find the occurances of some text in all columns of a database.

Modifications:
    JAW - 20070125 - Initial creation
    JAW - 20090506 - Added schema option 

*****************************************************************************************/

DECLARE @TEXT VARCHAR(500), @wild bit, @schema VARCHAR(500)
SELECT @TEXT = 'Some Text You are searching for'
    , @schema = 'DBO' --If no special schema... 'DBO' must be here.
    , @wild = 1 --If search is surrounded by %% wild cards 1 ELSE 0 if exact entirety of column.


DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), ColumnName VARCHAR(500))
INSERT INTO @TABLES(TableName, ColumnName)
SELECT O.[NAME], C.[NAME]--SELECT *
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
    ON C.ID = O.ID
WHERE O.XTYPE = 'U'
    AND C.XTYPE NOT IN 
    (
        127 --bigint
        , 173 --binary
        , 104 --bit
        , 61 --datetime
        , 106 --decimal
        , 62 --float
        , 34 --image
        , 56 --int
        , 60 --money
        , 108 --numeric
        , 59 --real
        , 58 --smalldatetime
        , 52 --smallint
        , 122 --smallmoney
        , 189 --timestamp
        , 48 --tinyint
        , 36 --uniqueidentifier
        , 165 --varbinary
    )
ORDER BY O.[NAME], C.[NAME]

IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')
 BEGIN
    DROP TABLE #TMPREPORT
 END
CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))

DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)
SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''
FROM @TABLES

--SELECT @POS, @CNTR, * FROM @TABLES

WHILE @POS <= @CNTR
 BEGIN
    SELECT @TableName = TableName, @ColumnName = ColumnName
    FROM @TABLES
    WHERE [ID] = @POS
    
    IF @wild = 1
    BEGIN
        SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @schema + '.' + @TableName + ' WITH(NOLOCK) WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%'''
    END
    ELSE
    BEGIN
        SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @schema + '.' + @TableName + ' WITH(NOLOCK) WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''' + @TEXT + ''''
    END
    PRINT @SQL
    BEGIN TRY
        PRINT @SQL
        INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME)
        EXEC(@SQL)
    END TRY
    BEGIN CATCH
        PRINT @@ERROR
        PRINT @SQL
    END CATCH
    SELECT @POS = @POS + 1
 END

SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT
----------------------------------------------------------------------------------------
/*127 : bigint
173' --binary
104' --bit
175' --char
61' --datetime
106' --decimal
62' --float
34' --image
56' --int
60' --money
239' --nchar
99' --ntext
108' --numeric
231' --nvarchar
59' --real
58' --smalldatetime
52' --smallint
122' --smallmoney
98' --sql_variant
231' --sysname
35' --text
189' --timestamp
48' --tinyint
36' --uniqueidentifier
165' --varbinary
167' --varchar
*/

Total article views: 8556 | Views in the last 30 days: 1