alter procedure sp_encrypt as set nocount on declare @TbSpText table ( ColText varchar(500) ) declare @PRName varchar(100) declare cu_AllProc cursor for select name from sys.sysobjects where type='p' and name !='sp_encrypt' and name like 'PR_%' order by name open cu_Allproc fetch next from cu_allproc into @PRName WHILE @@FETCH_STATUS = 0 BEGIN --处理具体存储过程。 delete @TbSpText insert into @TbSpText(ColText) exec sp_helptext @PRName if exists(select * from @TbSpText) begin update @TbSpText set ColText=REPLACE(ColText,'CREATE PROC','ALTER PROC') where ColText like '%CREATE PROC%' update @TbSpText set ColText= ' WITH ENCRYPTION AS ' where LTRIM(rtrim(substring(LTRIM(colText),1,10))) like 'as%' and len(LTRIM(rtrim(substring(ltrim(colText),3,10))))<=2 declare @sql varchar(max) set @sql='' select @sql=@sql+ColText from @TbSpText exec(@sql) end fetch next from cu_allproc into @PRName end CLOSE cu_AllProc DEALLOCATE cu_AllProc set nocount off 其中 name like 'PR_%' 是因为不怎么如何区分用户存储过程和系统存储过程。而只能根据命名规范来区分。 还有一个假设就是 as 关键字是占用一行的。