#title 코드성 데이터 수집
[[TableOfContents]]

==== 이 스크립튼 왜 만들었나? ====
데이터 웨어하우스에서는 비정규화된 테이블들이 많이 있다. 비정규화로 인해 코드값 자체도 데이터 웨어하우스 안에서도 관리를 하지 않게 되면 불일치를 가져오게 된다. 예를 들어, 어떤 컬럼에서 '게임이름'이라고 코드값이 세팅될 수 있고, 또 다른 컬럼에서는 '게임명'으로 코드값이 세팅될 수 있다. 그래서 아래와 같은 스크립트를 만들어 봤따~

==== 스크립트 ====
{{{
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
}}}