sql 2000在数据库所有表的所有列中查找一个字符串

Question

sql 2000在数据库所有表的所有列中查找一个字符串
比如 “eyes”这个字符 不知道在那个表的那一列里面 怎么查询在那里
麻烦帮我写一下语句

Answer 1

create procedure Findeyes
as
declare @ls_tblname varchar(100)
declare @ls_colname varchar(100)
declare @ls_sql varchar(4000)
declare @li_count integer

create table #tmp_tbl1
(
exists_count integer
)

create table #result
(
tbl_name varchar(100),
col_name varchar(100)
)

declare c1 cursor for
select sysobjects.name as table_name,syscolumns.name as col_name from sysobjects a,syscolumns b where a.id = b.id and b.xtype in (35,99,167,231)

open c1
fetch c1 into @as_tblname,@as_colname
while 1 = 1 begin
if @@sqlstatus = 2 begin
break
end

select @ls_sql = "select count(1) from " + @as_tblname + " where " +@as_colname +" like '%eyes%'"
insert into #tmp_tbl1 exec @ls_sql --搜索含eyes的字段

select @li_count = exists_count from #tmp_tbl1

if @li_count > 0 begin
insert into #result select @as_tblname,@as_colname --把含有该字段的表和字段名插到临时表去
end

end
close c1
select tbl_name,col_name from #result order by tbl_name,col_name --把含有该字段的表和字段名选出来

执行这个 procedure 在查询分析器里：exec Findeyes

Answer 2

查到是可以查，很慢的，用游标