_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › ロ襦覦轟覓語伎鮎蠍
/*
覯

1. @search_str1 ~ 5 蟾讌 覲 谿場 覓語伎 ロ.
2. @UpperLowerCK1 ~ 5 蟾讌 覲 覓語襯 蟲覿 蟆語  覓語伎 ロ. 
3. る  碁..
*/
declare 
	@dbname nvarchar(255)
,	@sql nvarchar(4000)
,	@search_str1 nvarchar(500)
,	@search_str2 nvarchar(500)
,	@search_str3 nvarchar(500)
,	@search_str4 nvarchar(500)
,	@search_str5 nvarchar(500)
,	@i int
,	@temp nvarchar(500)
,	@UpperLowerCK1 int
,	@UpperLowerCK2 int
,	@UpperLowerCK3 int
,	@UpperLowerCK4 int
,	@UpperLowerCK5 int

set @search_str1 = N'bcp '	-- 蠍一 谿場 覓語  (AND 一) 覓語 蟲覿? --> [b-B][c-C][p-P]
set @search_str2 = N''		-- 蠍一 谿場 覓語  (AND 一)
set @search_str3 = N''		-- 蠍一 谿場 覓語  (AND 一)
set @search_str4 = N''		-- 蠍一 谿場 覓語  (AND 一)
set @search_str5 = N''		-- 蠍一 谿場 覓語  (AND 一)
set @UpperLowerCK1 = 0		--1:覓語蟲覿, 0:覓語蟲覿
set @UpperLowerCK2 = 1		--1:覓語蟲覿, 0:覓語蟲覿
set @UpperLowerCK3 = 0		--1:覓語蟲覿, 0:覓語蟲覿
set @UpperLowerCK4 = 0		--1:覓語蟲覿, 0:覓語蟲覿
set @UpperLowerCK5 = 0		--1:覓語蟲覿, 0:覓語蟲覿

if @UpperLowerCK1 = 0 
begin
	set @i = 1
	set @temp = ''

	while(@i <= len(@search_str1))
	begin

		set @temp = @temp + 
				'[' + lower(substring(@search_str1, @i, 1)) + 
				upper(substring(@search_str1, @i, 1)) + ']'
		set @i = @i + 1
	end
	set @search_str1 = @temp
	set @temp = ''
end


if @UpperLowerCK2 = 0 
begin
	--@search_str2
	while(@i <= len(@search_str2))
	begin

		set @temp = @temp + 
				'[' + lower(substring(@search_str2, @i, 1)) + 
				upper(substring(@search_str2, @i, 1)) + ']'
		set @i = @i + 1
	end
	set @search_str2 = @temp
	set @temp = ''
end

if @UpperLowerCK3 = 0 
begin
	--@search_str3
	while(@i <= len(@search_str3))
	begin

		set @temp = @temp + 
				'[' + lower(substring(@search_str3, @i, 1)) + 
				upper(substring(@search_str3, @i, 1)) + ']'
		set @i = @i + 1
	end
	set @search_str3 = @temp
	set @temp = ''
end

if @UpperLowerCK4 = 0 
begin
	while(@i <= len(@search_str4))
	begin

		set @temp = @temp + 
				'[' + lower(substring(@search_str4, @i, 1)) + 
				upper(substring(@search_str4, @i, 1)) + ']'
		set @i = @i + 1
	end
	set @search_str4 = @temp
	set @temp = ''
end

if @UpperLowerCK5 = 0 
begin
	while(@i <= len(@search_str5))
	begin

		set @temp = @temp + 
				'[' + lower(substring(@search_str5, @i, 1)) + 
				upper(substring(@search_str5, @i, 1)) + ']'
		set @i = @i + 1
	end
	set @search_str5 = @temp
	set @temp = ''
end


if object_id('tempdb..#temp') is not null
	exec('drop table #temp')

create table #temp
(
	db_name nvarchar(500)
,	obj_name nvarchar(500)
)

declare cur cursor for
	--2000, 2005誤
	select name
	from master..sysdatabases

	--2005襷
	--select name
	--from master.sys.databases
	--where source_database_id is null
open cur
fetch next from cur into @dbname

while @@fetch_status = 0 
begin
	if @@version like '%SQL Server 2005%'
	begin
		set @sql = 
			N'select 
				' + '''' + @dbname + '''' + N'
			,	b.name ObjectNM
			from ' + @dbname + N'.sys.syscomments a inner join ' + @dbname + N'.sys.objects b
			on a.id = b.object_id
			where a.id > 0
			and text collate Korean_Wansung_CS_AS like ''%' + @search_str1 +'%'''
			
	end else
	begin
		set @sql = 
			N'select 
				' + '''' + @dbname + '''' + N'
			,	b.name ObjectNM
			from ' + @dbname + N'..syscomments a inner join ' + @dbname + N'..sysobjects b
			on a.id = b.id
			where a.id > 0
			and text collate Korean_Wansung_CS_AS like ''%' + @search_str1 +'%'''
	end

	if @search_str2 <> N'' set @sql = @sql + ' + ''' + @search_str2 + '%'''
	if @search_str3 <> N'' set @sql = @sql + ' + ''' + @search_str3 + '%'''
	if @search_str4 <> N'' set @sql = @sql + ' + ''' + @search_str4 + '%'''
	if @search_str5 <> N'' set @sql = @sql + ' + ''' + @search_str5 + '%'''
		

	print @sql
	insert #temp exec(@sql);
	
	fetch next from cur into @dbname
end

select distinct
	@@servername 覯覈
,	db_name DB覈
,	obj_name Object覈 
from #temp
order by 1,2,3

set @sql = N'
	select 
		@@servername 覯覈
	,	a.name	覈
	,	b.step_name 螻覈
	,	b.step_id	螻
	,	b.command	覈
	from msdb..sysjobs a inner join msdb..sysjobsteps b
	on a.job_id = b.job_id
	where b.command collate Korean_Wansung_CS_AS like ''%' + @search_str1 + '%'''

if @search_str2 <> N'' set @sql = @sql + ' + ''' + @search_str2 + '%'''
if @search_str3 <> N'' set @sql = @sql + ' + ''' + @search_str3 + '%'''
if @search_str4 <> N'' set @sql = @sql + ' + ''' + @search_str4 + '%'''
if @search_str5 <> N'' set @sql = @sql + ' + ''' + @search_str5 + '%'''
set @sql = @sql + ' order by 1,3'

print @sql
exec(@sql)

close cur
deallocate cur
go



--sp_helptext usp_SetGenericUser
/*
select *
from (select 'ccp' t) t
where t like '[bB][cC][pP]%'
*/
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

蟲郁 蟆 豌朱 螻旧 譟 蠏瑚 覦讌 襷螻 蟇一企. 覈 覲糾骸 螻旧 蠏碁訖 覦豌 れ 讌螳.