Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search for a text in all databases Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 9:42 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:25 AM
Points: 711, Visits: 219
Comments posted to this topic are about the item Search for a text in all databases
Post #449167
Posted Thursday, August 7, 2014 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:15 AM
Points: 2,734, Visits: 938
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'



Post #1600592
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse