/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
/***************************************************************************************************** * * 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'
URL: http://www.sqlservercentral.com/scripts/SQL+Server+2005/62066/