Video Screencast Help
Endpoint Management Community Blog

How to find text within database tables

Created: 28 Dec 2013 • Updated: 30 Dec 2013
SK's picture
0 0 Votes
Login to vote

Here is a handy SQL query that will allow you to search your database tables for a particular word or words (tested against SQL 2008 R2):

 

**********************************

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int

SELECT @Sargable = 'enter word or words here'

DECLARE insaneCursor CURSOR FOR
    SELECT c.TABLE_NAME, c.COLUMN_NAME
      FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
        ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
     WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')
       AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor
 
FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
                + '''' +  @TABLE_NAME + '''' + ','
                + '''' + @COLUMN_NAME + '''' + ','
                + 'COUNT(*) FROM [' + @TABLE_NAME  
                + '] WHERE [' + @COLUMN_NAME + '] Like '
                + ''''+ '%' + @Sargable + '%' + ''''
        --SELECT @SQL
        EXEC(@SQL)
        IF @@ERROR <> 0
            BEGIN
                SELECT @SQL
                SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
                GOTO Error
            END
        FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
    END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF