sqlserver 批量导出存储过程脚本

USE database
go
--创建表
CREATE TABLE procinfo_history(procname VARCHAR(256),texts NVARCHAR(max),tjdate CHAR(10),ver INT DEFAULT 0, seq INT IDENTITY)
go
--存储过程导出操作
CREATE PROC backup_procedure
AS 
BEGIN

CREATE TABLE #proc( text VARCHAR(max))

DECLARE @tjdate CHAR(10),@ver int
DECLARE @proc NVARCHAR(128)
SELECT @tjdate=CONVERT(CHAR(10),GETDATE(),121)
IF (SELECT count(1) FROM procinfo_history)=0
SELECT  @tjdate='1970-01-01'
SELECT @ver=MAX(ver) FROM dbo.procinfo_history
SELECT @ver=ISNULL(@ver,0)+1
DECLARE cur CURSOR FOR SELECT name
                               FROM sys.objects WHERE type='p' AND schema_id=1 AND modify_date>@tjdate
OPEN cur
FETCH NEXT FROM cur INTO @proc
WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #proc
INSERT #proc 
EXEC sp_helptext @proc
INSERT dbo.procinfo_history
    (
        procname,
        texts,
        tjdate,ver
    )
SELECT @proc,text,@tjdate,@ver FROM #proc
FETCH NEXT FROM cur INTO @proc
END
CLOSE cur
DEALLOCATE cur
DROP TABLE #proc
END

--每天定时备份
EXEC DBMonitor.dbo.backup_procedure