Search for a text in all databases

/*****************************************************************************************************
*
* Author Rafal Skotak
* Purpose Procedure searches for a text in all text-like columns in all or one selected database
* in all or selected table
* Created 2008.01.25 
* Updated 2011.02.18 - bug fixed (next table was displayed with current results)
*
******************************************************************************************************/

if exists(select * from sys.objects where object_id = object_id('dbo.proc_search_for_text') and type = 'P')
    drop procedure dbo.proc_search_for_text
go

create procedure dbo.proc_search_for_text
    @text varchar(max),
    @db_name sysname = NULL,
    @sel_table_name sysname = NULL
as
begin
    set nocount on

    ----------------------------------------------------------------------------
    -- check parameters

    if @text is NULL
    begin
        raiserror('Text is null', 16, 1)
        return
    end

    if @text = ''
    begin
        raiserror('Text is empty', 16, 1)
        return
    end

    if @db_name is not null
    begin
        if not exists(select * from master.sys.databases where name = @db_name and name not in ('tempdb'))
        begin
            raiserror('Database does not exist or can not be scanned', 16, 1)
            return
        end
    end

    ------------------------------------------------------------------------------------------------
    -- get databases list
    
    create table #temp_dbs_table
    (
        db_name sysname not null primary key
    )

    insert into #temp_dbs_table (db_name) select name from master.sys.databases where name not in ('tempdb') and (@db_name is null or (@db_name is not null and @db_name = name))

    declare @current_db_name sysname

    set @current_db_name = N''

    create table #temp_columns_table
    (
        table_id int not null,
        schema_name sysname not null,
        table_name sysname not null,
        column_name sysname not null,
        mod_flag tinyint not null default 1

        primary key(schema_name, table_name, column_name)
    )

    while @current_db_name is not null
    begin
        set @current_db_name = NULL

        -----------------------------------------------------------------------------------------------
        -- move to next database

        select top 1 @current_db_name = db_name from #temp_dbs_table

        if @current_db_name is null
            break
        
        ---------------------------------------------------------------------------------------------
        -- get columns list

        truncate table #temp_columns_table

        declare @n_cmd nvarchar(max)

        if @sel_table_name is NULL
        begin
            set @n_cmd = N'insert into #temp_columns_table 
            select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
                         from [' + @current_db_name + '].sys.schemas as ss inner join
                         [' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join 
                         [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
                            system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */) 
                            order by ss.name, st.name, sc.name'
        end
        else
        begin
            set @n_cmd = N'insert into #temp_columns_table 
            select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
                         from [' + @current_db_name + '].sys.schemas as ss inner join
                         [' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join 
                         [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
                            system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */) 
                        and st.name = ''' + @sel_table_name + ''' order by ss.name, st.name, sc.name'
        end

        -- print @n_cmd
        
        exec sp_executesql @n_cmd

        ----------------------------------------------------------------------------------
        -- cycle through columns

        declare @n_sec_cmd nvarchar(max)
        declare @n_thi_cmd nvarchar(max)
        declare @schema_name sysname
        declare @table_name sysname
        declare @column_name sysname
        declare @table_id int
        declare @prev_table_id int

        declare @previous_schema_name sysname
        declare @previous_table_name sysname

        set @table_id = 0
        set @prev_table_id = -1

        while @table_id >= 0
        begin

            set @table_id = NULL
        
            select top 1 
                @table_id = table_id,
                @schema_name = schema_name,
                @table_name = table_name,
                @column_name = column_name
            from
                #temp_columns_table
            where
                mod_flag <> 0
            order by
                schema_name,
                table_name,    
                column_name

            if @table_id is NULL
                set @table_id = -1

            if @table_id <> @prev_table_id
            begin
                -------------------------------------------------------------------------------------
                -- execute previous command

                if @prev_table_id > 0
                begin
                    set @n_thi_cmd = 'if exists (' + @n_sec_cmd + ') ' + char(13) 
                        + 'begin ' + char(13) 
                        + 'select ''' 
                        + @current_db_name + ''' as database_name, ''' 
                        + @previous_schema_name + ''' as schema_name, ''' 
                        + @previous_table_name + ''' as table_name' 
                        + char(13) 
                        + @n_sec_cmd + char(13) + 'end'

                    print @n_thi_cmd

                    exec sp_executesql @n_thi_cmd
                end

                set @n_sec_cmd = 'select * from [' + @current_db_name + '].[' + @schema_name + '].[' + @table_name + '] where ([' + @column_name + '] is not null and [' + @column_name + '] like ''%' + @text + '%'') ' 
            end
            else
            begin
                set @n_sec_cmd = @n_sec_cmd + char(13) + ' or ([' + @column_name + '] is not null and replace(upper(cast([' + @column_name + '] as varchar(64))), ''-'', '''' ) like ''%' + @text + '%'') ' 
            end

            set @prev_table_id = @table_id

            update #temp_columns_table set mod_flag = 0 where table_id = @table_id and column_name = @column_name

            set @previous_schema_name = @schema_name
            set @previous_table_name = @table_name
        end

        delete from #temp_dbs_table where db_name = @current_db_name
    end

    ---------------------------------------------------------------------------------
    -- cleanup

    drop table #temp_columns_table

    drop table #temp_dbs_table

end
go

-- examples:

-- exec dbo.proc_search_for_text 'Nathan'

-- exec dbo.proc_search_for_text 'Everett', 'AdventureWorks'