Revision: 42330
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
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