实用的存储历程之二[MSSQL防范]

实用的存储历程之二

笔者工作的公司采取的是SQLServer数据库,每天都要处理大量的数据,由于笔者进公司的时间对比晚,公司现有的大部份的程序都是从前的程序员留下的,因为他们没有相关的文档,笔者关于后台数据库的很多表的构造和数据都不甚理解,给平常的保护造成了很大的麻烦.草地chin ai tp owercCDBR

在对后台数据库举行研究的历程中,我需求得到数据库的某些相关信息,比方,公司的数据库中有几个表存放笔者的个人资料,像人事表、工资表、部门表等等,但具体是哪些表,就不是很清楚了,假如要一个一个表地找,大概天亮了也找不完,所以我决意做一个通用的存储历程,能对当前数据库全部字符型字段举行遍历,找出切确匹配含有要查找字符串的表和字段,并且摆列出来.比方,人事表的Name字段,工资表的Salary_Name字段,部门表的Employe_Name字段都有笔者的名字,我但愿能把这些找出来.存储历程以下:草地chin ai tp owercCDBR

IF EXISTS (SELECT name FROM sysobjects 草地chin ai tp owercCDBR

         WHERE name = 'searchname' AND type = 'P')草地chin ai tp owercCDBR

   DROP PROCEDURE searchname草地chin ai tp owercCDBR

Go草地chin ai tp owercCDBR

create procedure searchname @sname varchar(10)草地chin ai tp owercCDBR

As草地chin ai tp owercCDBR

begin草地chin ai tp owercCDBR

create table #TableList(草地chin ai tp owercCDBR

  tablename  char(200),草地chin ai tp owercCDBR

  colname char(200)草地chin ai tp owercCDBR

)草地chin ai tp owercCDBR

 草地chin ai tp owercCDBR

declare @table varchar(200)草地chin ai tp owercCDBR

declare @col varchar(200)草地chin ai tp owercCDBR

 草地chin ai tp owercCDBR

set nocount on草地chin ai tp owercCDBR

declare curTab scroll cursor for select name from sysobjects where xtype='u'草地chin ai tp owercCDBR

open curTab草地chin ai tp owercCDBR

fetch next from curTab into @table草地chin ai tp owercCDBR

while @@FETCH_STATUS=0草地chin ai tp owercCDBR

begin草地chin ai tp owercCDBR

  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))草地chin ai tp owercCDBR

  open curCol草地chin ai tp owercCDBR

  fetch next from curCol into @col草地chin ai tp owercCDBR

  while @@FETCH_STATUS=0草地chin ai tp owercCDBR

  begin草地chin ai tp owercCDBR

    execute('insert into #TableList select ''+@table+'',''+@col+'' from '+@table+' where '+@col+'=''+@sname+'')草地chin ai tp owercCDBR

    fetch next from curCol into @col草地chin ai tp owercCDBR

  end草地chin ai tp owercCDBR

  close curCol草地chin ai tp owercCDBR

  deallocate curCol草地chin ai tp owercCDBR

  fetch next from curTab into @table草地chin ai tp owercCDBR

end草地chin ai tp owercCDBR

close curTab草地chin ai tp owercCDBR

deallocate curTab草地chin ai tp owercCDBR

set nocount off草地chin ai tp owercCDBR

select  distinct * from #TableList草地chin ai tp owercCDBR

drop table #tablelist  草地chin ai tp owercCDBR

end草地chin ai tp owercCDBR

调用很简单,如想找笔者的名字,调用SearchName ‘forgot2000’便可,查找速度视乎当前数据库的大小而定.但愿这个存储历程能对大家有所帮忙吧.本人E-mail:coolforgot@sina.com,QQ:33563255,但愿能跟大家共同交流,谢谢!草地chin ai tp owercCDBR

本存储历程在SQLServer7.0/2000下通过.草地chin ai tp owercCDBR

 草地chin ai tp owercCDBR