_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
FrontPage › DB豌伎貊一危一鮎蠍
|
|
[edit]
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
鏤
|
蠍一螻 覈 蟆 螳朱 企伎 る蟆 蟆一 伎 . |