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