#title 저장 프로시저 및 작업에서 특정 문자열 찾기
{{{
/*
사용법

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]%'
*/
}}}