sql 清存储过程注释

DECLARE curSPName CURSOR FOR SELECT [name] FROM sysobjects WHERE xtype='P'
DECLARE @SPName nvarchar(200)
DECLARE @Table TABLE([Text] nvarchar(200))
DECLARE @ErrTab TABLE(ERSP nvarchar(200),
ErrorNumber nvarchar(200),
ErrorSeverity nvarchar(200),
ErrorState nvarchar(200),
ErrorProcedure nvarchar(200),
ErrorLine nvarchar(200),
ErrorMessage nvarchar(200))
DECLARE @Code nvarchar(MAX)
OPEN curSPName
FETCH NEXT FROM curSPName INTO @SPName
WHILE @@FETCH_STATUS=0
BEGIN
--SELECT @SPName
INSERT INTO @Table EXEC sp_helptext @SPName
--SELECT [Text] FROM @Table AS [Table]
SET @Code=''
SELECT @Code=@Code+REPLACE(dbo.RegExpReplace([Text], '--.*$', ''), 'CREATE PROCEDURE ', 'ALTER PROCEDURE ')+' ' FROM @Table AS [Table]
--SELECT @Code
BEGIN TRY
EXEC (@Code)
END TRY
BEGIN CATCH
INSERT INTO @ErrTab(ERSP,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage)
SELECT
@SPName AS ERSP,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
DELETE FROM @Table
FETCH NEXT FROM curSPName INTO @SPName
END
CLOSE curSPName
DEALLOCATE curSPName
SELECT * FROM @ErrTab