Search for a text in all databases

  • Comments posted to this topic are about the item Search for a text in all databases

  • Old but gold!

    I like your script and added the feature do search uniqueidentifiers (because I needed it)

    and added to skyp not online DataBases and some sebug in case it fails to execute the search for the string

    Also I removed the hifen replace since it does not work when I seek for a Guid and you can use wildcards at the string parameter anyway

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

    *

    * 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)

    * Updated 2014.08.06 - Extended to find uniqueidentifiers, don't replace hifen and skyp offline DB (by Jean Bulinckx)

    * Updated 2014.08.07 - Added a break and some debug info in case of error (by Jean Bulinckx)

    *

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

    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 state_desc = 'ONLINE'

    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, isc.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

    join [' + @current_db_name + '].INFORMATION_SCHEMA.columns as isc on isc.TABLE_NAME = st.name and isc.TABLE_SCHEMA = ss.name

    where

    isc.DATA_TYPE = ''UNIQUEIDENTIFIER'' OR DATA_TYPE LIKE ''%CHAR%'' OR DATA_TYPE LIKE ''%TEXT%''

    order by ss.name, st.name, isc.column_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, isc.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

    join [' + @current_db_name + '].INFORMATION_SCHEMA.columns as isc on isc.TABLE_NAME = st.name and isc.TABLE_SCHEMA = ss.name

    where

    isc.DATA_TYPE = ''UNIQUEIDENTIFIER'' OR DATA_TYPE LIKE ''%CHAR%'' OR DATA_TYPE LIKE ''%TEXT%''

    and st.name = ''' + @sel_table_name + '''

    order by ss.name, st.name, isc.column_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

    if @@ERROR > 0

    begin

    print @n_thi_cmd

    select *

    from #temp_columns_table

    where table_name = @previous_table_name

    set noexec on

    set noexec off

    end

    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 upper(cast([' + @column_name + '] as varchar(max))) 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'

    -- exec dbo.proc_search_for_text '11111111-2222-3333-4444-555555555555', 'MyDatabase'

    -- exec dbo.proc_search_for_text '11111111%2222', 'MyDatabase'

  • Thanks for the script.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply