Search for a specified GUID (or its part) in all databases (updated)

/*****************************************************************************************************
*
* Author Rafal Skotak
* Purpose Procedure searches for a GUIDs containing specified number in all or one selected database
* in all or selected table
* Date 2008.01.24
* 2008.04.17 Updated - regarding Barnold's remark - @guid is now a varchar(36)
* 2011.02.18 Updated - fixed bug
******************************************************************************************************/

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

create procedure dbo.proc_search_for_guid
@guid varchar(36),
@db_name sysname = NULL,
@sel_table_name sysname = NULL
as
begin
set nocount on

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

if @guid is NULL
begin
    raiserror('Guid is null', 16, 1)
    return
end

set @guid = upper(@guid)

set @guid = replace(@guid, '-', '')

if @guid not like replicate('[0-9,A-F]', len(@guid))
begin
    raiserror('Invalid guid part - please use only digits and letters A-F', 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 <> '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 <> '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 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 = 36 /* uniqueidentifier */ order by ss.name, st.name, sc.name'
         end
         else
         begin
             set @n_cmd = N'insert into #temp_columns_table 
                 select 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 = 36 /* uniqueidentifier */ 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 replace(upper(cast([' + @column_name + '] as varchar(64))), ''-'', '''' ) like ''%' + @guid + '%'') ' 
             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 ''%' + @guid + '%'') ' 
             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

-- example (search for a specified GUID template in any database):

exec dbo.proc_search_for_guid '123'


-- example (search for a specified GUID template in the selected database):

exec dbo.proc_search_for_guid 'abc', 'AdventureWorks'

-- example (search for a specified GUID template in tables with specified name of selected database):

exec dbo.proc_search_for_guid 'abc0', 'AdventureWorks', 'Address'

-- example (search for a specified GUID template in tables with specified name of selected database):

exec dbo.proc_search_for_guid '123-abc', 'AdventureWorks', 'Address'