1 개요

2 코드

• 코드는 데이터가 많지 않다는 가정에서 만든 스크립트
  
• 찾고자 하는 키워드와 몇 Row 이하까지 코드 테이블로 설정할 것인지에 대한 기준만 세우면 된다.
  
  

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('DIM_SALES_Org', 'DIM_PUBLISH_GENDER')
	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