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