January 29, 2008 at 9:42 pm
Comments posted to this topic are about the item Search for a text in all databases
August 7, 2014 at 6:37 am
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'
May 17, 2016 at 6:33 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply