SQL实现查询未加密存储过程脚本

/*   编写存储过程，查询未加密的存储过程脚本文本

*/ 

if exists(select name from sys.sysobjects where xtype='p' and name='proce_text')
drop procedure proce_text
go
create procedure proce_text
as
create table #procedure_detail(id int identity(1,1),texts nvarchar(max))

---使用游标，遍历所有未加密存储过程

declare @sql varchar(8000),@var varchar(1000)
set @sql=''
declare @name varchar(30)
set @name=''
declare cur1 cursor for
select distinct A.name from sys.sysobjects A,sys.syscomments B
where a.id=b.id and a.xtype='p' and B.encrypted=0
open cur1
fetch next from cur1 into @name
 while @@fetch_status=0
  begin
   create table #1(a varchar(3000))
   insert into #1(a) exec sp_helptext @name
    declare cur2 cursor for
     select a from #1
      open cur2
       fetch next from cur2 into @var
        while @@fetch_status=0
         begin
         set @sql=@sql+' '+@var
         fetch next from cur2 into @var
         end
    close cur2
    deallocate cur2
    drop table #1
    insert #procedure_detail(texts) select @sql
    set @name=''
    set @sql=''
   fetch next from cur1 into @name
  end
  close cur1
  deallocate cur1
  select * from #procedure_detail

--查询结果
exec proce_text