Contents

1 ろ襴渚 襷れ?
2 ろ襴渚


1 ろ襴渚 襷れ? #

一危 危一れ 觜蠏 企れ 襷 . 觜蠏襦 誤 貊螳 豌企 一危 危一 蟯襴襯 讌 蟆 覃 覿殊襯 螳語り . 襯 れ, 企 貉殊 '蟆企'企手 貊螳 誤 螻, るジ 貉殊 '蟆覈'朱 貊螳 誤 . 蠏碁 螳 ろ襴渚碁ゼ 襷れ 覺る~

2 ろ襴渚 #

set nocount on
set statistics io off

declare 
	@column_value varchar(2000)
,	@rowcnt int;

--==============================================================
-- 誤覿覿 
--==============================================================

set @column_value = '蟆'
set @rowcnt = 2000 --2000蟇 危 企襷 蟆蟆.

--==============================================================
-- 誤覿覿 
--==============================================================

select 
	table_catalog
,	table_schema
,	table_name
,	column_name
,	data_type
,	convert(varchar(4000), '') column_value
into #rs
from information_schema.columns
where 1=0
	
declare top_cur cursor for
	select 
		object_name(id) obj_name
	from sys.sysindexes
	where rowcnt <= @rowcnt 
	and rowcnt > 0
	and id > 100
	and object_name(id) not like 'fact%'
	--and object_name(id) in('企1', '企2')
	group by id
	
declare 
	@sql1 varchar(max)
,	@tname varchar(500)
,	@sch varchar(500)
,	@colname varchar(500)

set @sch = 'dbo'
--set @tname = 'DIM_AdultGames'
open top_cur
fetch next from top_cur into @tname

while @@fetch_status = 0
begin

	--讓曙擦 
	create table #coltmp(colval varchar(4000))
	create table #col(colname varchar(4000), colval varchar(4000))
	
	declare cur cursor for
		select 
			column_name
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sch

	set @sch = '''' + @sch + ''''
	set @tname = '''' + @tname + ''''

	open cur
	fetch next from cur into @colname

	while @@fetch_status = 0
	begin
		set @colname = '''' + @colname + ''''

		set @sql1 = '
			declare @sql varchar(8000);
			set @sql = '''';
			
			select @sql = @sql + col
			from (
				select 
					''select convert(varchar(4000), ['' + column_name + 
					'']) from ['' + table_catalog + ''].['' + 
					table_schema + ''].['' + 
					table_name + ''] union all '' col
				from information_schema.columns
				where table_name = ' + @tname + '
				and column_name = ' + @colname + '
				and table_schema = ' + @sch + '
			) t
			set @sql = reverse(substring(reverse(@sql), 10, 8000))
			exec(@sql)
			'
		--print @sql1
		insert #coltmp exec(@sql1)
		
		insert #col
		select @colname, colval from #coltmp
		
		truncate table #coltmp
		set @colname = ''
		fetch next from cur into @colname
	end

	--select * from #col
	insert #rs
	select 
		a.table_catalog
	,	a.table_schema
	,	a.table_name
	,	a.column_name
	,	a.data_type
	,	convert(varchar(4000), b.colval) column_value
	from information_schema.columns a 
		inner join #col b
			on a.column_name = replace(b.colname, '''', '')
	where a.table_name = replace(@tname, '''', '')
	and a.table_schema = replace(@sch, '''', '')

	drop table #col
	drop table #coltmp

	close cur
	deallocate cur
	--讓曙擦 
	set @sch = replace(@sch, '''', '')
	print @tname
	fetch next from top_cur into @tname
end

select distinct * from #rs 
where column_value like '%' + @column_value + '%'
drop table #rs

close top_cur
deallocate top_cur