Return to Snippet

Revision: 42330
at March 3, 2011 08:07 by ezerick


Initial Code
/*****************************************************************************************************
*
* 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)
*
******************************************************************************************************/


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 (@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, sc.name as 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 inner join 
������������������������ [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
����������������������������system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */) 
����������������������������order by ss.name, st.name, sc.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, sc.name as 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 inner join 
������������������������ [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
����������������������������system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */) 
������������������������and st.name = ''' + @sel_table_name + ''' order by ss.name, st.name, sc.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
����������������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 replace(upper(cast([' + @column_name + '] as varchar(64))), ''-'', '''' ) 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'

Initial URL
http://www.sqlservercentral.com/scripts/SQL+Server+2005/62066/

Initial Description


Initial Title
Search for a text in all databases - SQLServerCentral

Initial Tags
database, search

Initial Language
SQL