实用的存储过程2
在对后台数据库进行研究的过程中，我需要得到数据库的某些相关信息，比如，公司的数据库中有几个表存放笔者的个人资料,像人事表、工资表、部门表等等，但具体是哪些表，就不是很清楚了，如果要一个一个表地找，可能天亮了也找不完，所以我决定做一个通用的存储过程，能对当前数据库所有字符型字段进行遍历，找出精确匹配含有要查找字符串的表和字段，并且罗列出来。比如，人事表的Name字段，工资表的Salary_Name字段，部门表的Employe_Name字段都有笔者的名字，我希望能把这些找出来。存储过程如下：
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'searchname' AND type = 'P')
DROP PROCEDURE searchname
Go
create procedure searchname @sname varchar(10)
As
begin
create table #TableList(
tablename char(200),
colname char(200)
)
declare @table varchar(200)
declare @col varchar(200)
set nocount on
declare curTab scroll cursor for select name from sysobjects where xtype='u'
open curTab
fetch next from curTab into @table
while @@FETCH_STATUS=0
begin
declare curCol scroll cursor for select name from syscolumns where (xtype=175 or xtype=167) and (id in (select id from sysobjects where name=@table))
open curCol
fetch next from curCol into @col
while @@FETCH_STATUS=0
begin
execute('insert into #TableList select ''+@table+'',''+@col+'' from '+@table+' where '+@col+'=''+@sname+'')
fetch next from curCol into @col
end
close curCol
deallocate curCol
fetch next from curTab into @table
end
close curTab
deallocate curTab
set nocount off
select distinct * from #TableList
drop table #tablelist
end
from:asp学习网/title:实用的存储过程2/ time:2006-8-6 16:09:16
本文主题实用的存储过程2