我们平时做调查的时候,经常会疑惑某个数据到底是从哪来的,往往使用事件查看器之类的捕捉sql来找,其实我们也可以用下边这条sql试试。不过在数据量大的时候确实是比较慢。
drop table #
declare @t varchar(255),@c varchar(255)
create table # (name varchar(256),cols varchar(4000))
declare table_cursor cursor for
select a.name,b.name from sysobjects a,syscolumns b
where a.id=b.id and a.xtype='U' and b.xtype in (35,99,167,175,231,239)
--上边这几个数字是我从sys.types查出来的,限制了只搜索文本类型,具体使用的时候可以自行修改
open table_cursor fetch next from table_cursor
into @t,@c
while(@@fetch_status=0)
begin
exec('
set nocount on
if exists(select top 1 '+@c+' from [' + @t + '] where [' + @c + '] like ''%lond%'')
begin
if not exists(select 1 from # where name='''+@t+''')
insert into # select '''+@t+''','''+@c+'''
else
update # set cols=cols+'','+@c+''' where name='''+@t+'''
end
')
fetch next from table_cursor into @t,@c
end
close table_cursor deallocate table_cursor;
select * from #