_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › DB豌伎貊一危一鮎蠍

Contents

1
2


1 #

  • 貊 一危郁 襷讌 る 螳 襷 ろ襴渚
  • 谿剰 れ 覈 Row 危蟾讌 貊 企襦 れ 蟆語 蠍一襷 語磯 .

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('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
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

磯Μ蟆 蟆 るジ 蟆 譴 蟆訖企. (蠍伎る蠏)