Search on All DB

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

/*         Procedure of search of a phrase on all database                                                              */

/*            Originally developed by Oufimtsev Gleb, MCSE                                                            */

/*                                                                                                                                                                  */

/*            Updated by David Musgrave, Microsoft                                                                            */

/*            from feedback from Eduardo Barbosa & Marc K                                                            */

/*                                                                                                                                                                  */

/*            Last Modified: 26-Jul-2013                                                                                                  */

/*                                                                                                                                                                  */

/*  Search for spSearchOnAllDB at http://aka.ms/Dev4DynGP for more info                       */

/*                                                                                                                                                                   */

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

 

if exists (select * from sysobjects where id = object_id('dbo.spSearchOnAllDB') )

    drop procedure dbo.spSearchOnAllDB

GO

 

CREATE PROCEDURE spSearchOnAllDB @phrase varchar(8000), @OutFullRecords bit = 0 AS

 

/*

   To apply so: 

      exec  spSearchOnAllDB 'Sugar%'

      exec  spSearchOnAllDB '%soft%'

      exec  spSearchOnAllDB '_5234_57%', 1

      exec  spSearchOnAllDB M_cro_oft

*/

 

declare @sql varchar(8000)

declare @tbl varchar(128) 

declare @col varchar(128)

declare @id_present bit

 

declare @is_char_phrase bit

declare @min_len int

declare @loop_idx int

declare @loop_chr char(1)

 

set nocount on

 

if IsNull(@phrase, '') = '' begin

    raiserror('Phrase is absent', 16, -1)

    return

end

 

-- Handle Quotes passed in the search string

set @phrase = replace(@phrase, '''', '''''')

 

select @loop_idx = 1, @is_char_phrase = 0, @min_len = 0 

 

while @loop_idx <= LEN(@phrase) begin

    set @loop_chr = SUBSTRING(@phrase, @loop_idx,1)

    if @loop_chr not in ('%', '_') 

        set @min_len = @min_len + 1

    if @is_char_phrase = 0 and @loop_chr not in ('%', '_', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')  

        set @is_char_phrase = 1

    set @loop_idx = @loop_idx + 1

end 

 

create table #tbl_res 

            (TableName        varchar(128) not NULL,

             ColumnName        varchar(128) not NULL,

             Id                int NULL,

             ColumnValue    varchar(7500) not NULL)

 

create table #tbl_res2 

            (TableName        varchar(128) not NULL,

             ColumnName        varchar(128) not NULL,

             Id                int NULL,

             ColumnValue    varchar(7500) not NULL)

 

declare CRR cursor local fast_forward for

    select t.name, c.name, 1 

    from sysobjects t, syscolumns c 

    where t.type = 'U'

    and c.id = t.id

    and c.status&0x80 = 0 -- Not IDENTITY

    and exists (select * from syscolumns c2 where t.id = c2.id and c2.status&0x80 = 0x80 and c2.xtype in (48, 52, 56))

    and (  (@is_char_phrase = 1 and c.xtype in (175, 239, 99, 231, 35, 167) and c.length >= @min_len) -- char only

        or (@is_char_phrase = 0 and c.xtype not in (34, 165, 173, 189, 61, 58, 36))) -- char and numeric

    union 

    select t.name, c.name, 0 

    from sysobjects t, syscolumns c 

    where t.type = 'U'

    and c.id = t.id

    and not exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80 = 0x80 and c2.xtype in (48, 52, 56))

    and (  (@is_char_phrase = 1 and c.xtype in (175, 239, 99, 231, 35, 167) and c.length >= @min_len) -- char only

        or (@is_char_phrase = 0 and c.xtype not in (34, 165, 173, 189, 61, 58, 36))) -- char and numeric

    order by 1, 2

 

open CRR

fetch CRR into @tbl, @col, @id_present

while @@FETCH_STATUS = 0 begin

    if @OutFullRecords = 0 begin

        set @sql = 'insert into #tbl_res (TableName, ColumnName, Id, ColumnValue) '

                    + 'select ''[' + @tbl + ']'', ''[' + @col + ']'', '

        if @id_present = 1

            set @sql = @sql + 'IDENTITYCOL, '

        else 

            set @sql = @sql + 'NULL, ' 

        set @sql = @sql + 'convert(varchar(7500), [' + @col + ']) '

                        + 'from [' + @tbl + '] (nolock) '

                        + 'where convert(varchar(8000), [' + @col + ']) like ''' + @phrase + ''' '

    end

    if @OutFullRecords = 1 begin

        set @sql = 'if exists (select * from [' + @tbl + '] (nolock) '

                 + 'where convert(varchar(8000), [' + @col + ']) like ''' + @phrase + ''') '

                 + 'select ''[' + @tbl + ']'' TableName, ''[' + @col+ ']'' ColumnName, * '

                 + 'from [' + @tbl + '] (nolock) where convert(varchar(8000), [' + @col + ']) like ''' + @phrase + ''' '

    end

    exec(@sql)

    fetch CRR into @tbl, @col, @id_present

end

close CRR

deallocate CRR

 

if @OutFullRecords = 0 begin

    -- For the clients supporting new types:

    --exec('select * from #tbl_res order by 1,2,3')

 

    -- For the clients who are not supporting new types:

    INSERT #tbl_res2

    select TableName, ColumnName, Id, convert(varchar(255),ColumnValue) ColumnValue from #tbl_res

 

    /** exec('select TableName, ColumnName, Id, convert(varchar(255),ColumnValue) ColumnValue from #tbl_res order by 1,2,3')**/

end

  

drop table #tbl_res 

 

/***Select Statement to show tables***/

 

select TableName, ColumnName, ColumnValue from #tbl_res2 group by TableName, ColumnName, ColumnValue

    order by TableName

 

truncate table #tbl_res2

drop table #tbl_res2

RETURN

 

GO

 

--GRANT EXECUTE ON dbo.spSearchOnAllDB TO DYNGRP

 

/* Copyright © Microsoft Corporation.  All Rights Reserved.                     */

/* This code released under the terms of the                                    */

/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

 

--exec  spSearchOnAllDB '100XL%', 1

--exec  spSearchOnAllDB '%''%', 0