SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search for a text in all databases


Search for a text in all databases

Author
Message
skra
skra
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 228
Comments posted to this topic are about the item Search for a text in all databases
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 997
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'




Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22330 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search