sql 2000在数据库所有表的所有列中查找一个字符串
答案:2 悬赏:70 手机版
解决时间 2021-03-21 23:52
- 提问者网友:趣果有间
- 2021-03-21 15:16
sql 2000在数据库所有表的所有列中查找一个字符串
最佳答案
- 五星知识达人网友:由着我着迷
- 2021-03-21 15:27
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
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
全部回答
- 1楼网友:长青诗
- 2021-03-21 15:57
查到是可以查,很慢的,用游标
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯