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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy