Bergius

--
-- Finds data in NTEXT/TEXT/NVARCHAR/VARCHAR columns and lists table and column names
-- Usage: find_tables_and_columns 'text to find'
--    or: find_tables_and_columns @search_string = 'text to find', @keep_data = 1
--
CREATE PROCEDURE find_tables_and_columns
        @search_string varchar(255) = NULL,
        @keep_data bit = 0
AS
        SET NOCOUNT ON

        DECLARE @report_table varchar(255), @timestamp varchar(12)

        SET @timestamp = REPLACE(REPLACE(REPLACE(convert(varchar, getdate(), 20), ':', ''), '-', ''), ' ', '')
        SET @report_table = 'find_tables_and_columns_' + @timestamp

        IF (@search_string IS NULL)
                SET @search_string = '<script'

        -- create report table
        EXEC('IF NOT EXISTS(SELECT name FROM sysobjects WHERE name = ''' + @report_table + ''' AND xtype=''U'') CREATE TABLE [' + @report_table + '] (table_name varchar(255), column_name varchar(255))')

        -- truncate report table
        EXEC('DELETE FROM [' + @report_table + ']')

        -- find all tables and columns of types ntext, text, nvarchar and varchar
        DECLARE @T varchar(255),@C varchar(255)
        DECLARE Table_Cursor CURSOR FOR
                SELECT a.name, b.name
                FROM sysobjects a, syscolumns b
                WHERE a.id = b.id
                        AND a.xtype = 'u'
                        AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167)
        OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T, @C

        -- loop through all tables and columns and see where data matches search string
        WHILE (@@FETCH_STATUS = 0) BEGIN
                IF (@T != @report_table) -- ignore report table
                        EXEC('IF (EXISTS(SELECT [' + @C + '] FROM [' + @T + '] WHERE [' + @C + '] LIKE ''%' + @search_string + '%'')) INSERT INTO [' + @report_table + '] (table_name, column_name) VALUES (''' + @T + ''', ''' + @C + ''')')
                FETCH NEXT FROM Table_Cursor INTO @T, @C
        END

        CLOSE Table_Cursor
        DEALLOCATE Table_Cursor

        SET NOCOUNT OFF

        -- output report
        EXEC('SELECT table_name, column_name FROM [' + @report_table + ']')

        SET NOCOUNT ON

        -- drop data or select table name
        IF (@keep_data = 0) BEGIN
                EXEC('DROP TABLE [' + @report_table + ']')
        END
        ELSE BEGIN
                SELECT @report_table AS report_table
        END
GO

--
-- Finds data in NTEXT/TEXT/NVARCHAR/VARCHAR columns and outputs matching rows
-- Usage: find_rows 'text to find'
--
CREATE PROCEDURE find_rows
        @search_string varchar(255) = NULL
AS
        SET NOCOUNT ON

        IF (@search_string IS NULL)
                SET @search_string = '<script'

        -- find all tables and columns of types ntext, text, nvarchar and varchar
        DECLARE @T varchar(255),@C varchar(255)
        DECLARE Table_Cursor CURSOR FOR
                SELECT a.name, b.name
                FROM sysobjects a, syscolumns b
                WHERE a.id = b.id
                        AND a.xtype = 'u'
                        AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167)
        OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T, @C

        SET NOCOUNT OFF

        -- loop through all tables and columns and see where data matches search string
        WHILE (@@FETCH_STATUS = 0) BEGIN
                EXEC('IF (EXISTS(SELECT [' + @C + '] FROM [' + @T + '] WHERE [' + @C + '] LIKE ''%' + @search_string + '%'')) SELECT ''' + @T + ''' AS tablename, ''' + @C + ''' AS columnname, * FROM [' + @T + '] WHERE [' + @C + '] LIKE ''%' + @search_string + '%''')
                FETCH NEXT FROM Table_Cursor INTO @T, @C
        END

        CLOSE Table_Cursor
        DEALLOCATE Table_Cursor
GO