实用的存储过程

jackey  2009.06.26 实例 存储过程
实现功能：根据用户输入的信息查找出 信息所在的的表 和信息所在的字段

测试方法 3种：
searchname '52150000010000000002'
exec  searchname '52150000010000000002'
execute searchname '52150000010000000002'

存储过程
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
         print('insert into #TableList select '''+@table+''','''+@col+''' from '+@table+' where '''+@table+''','''+@col+''' from '+@table+' where