存儲過程代碼自動備份

  首先在master庫下建立表ProcSqlTable和BackProcSqlTable。ProcSqlTable存放存儲過程當前版本代碼，BackProcSqlTable存放歷史版本代碼。

USE [master]
GO
CREATE TABLE [dbo].[BackProcSqlTable](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY ,--編號
    [dbName] [nvarchar](150) NOT NULL,--資料庫名
    [ProcSQL] [ntext] NOT NULL,--存儲過程的SQL
    [ProcName] [nvarchar](150) NOT NULL,--存儲過程名字
    [AlterDate] [datetime] NOT NULL,--修改時間
    [AlterUser] [nvarchar](150) NULL--修改人
)
GO



USE [master]
GO
CREATE TABLE [dbo].[ProcSqlTable](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,--編號
    [dbName] [nvarchar](150) NOT NULL,--數據名
    [ProcSQL] [ntext] NOT NULL,--存儲過程SQL
    [ProcName] [nvarchar](150) NOT NULL--存儲過程名字
)
GO

　 接下來需要建立兩個庫級(DDL)觸發器：tr_saveProcSql和tr_saveBackProcSql。tr_saveProcSql將新建存儲過程代碼寫入ProcSqlTable，tr_saveBackProcSql將ProcSqlTable表中保存的代碼寫到BackProcSqlTable中作為歷史版本代碼，同時將存儲過程當前代碼更新到ProcSqlTable。

USE [master]
GO
create trigger [tr_saveProcSql]
on all server --作用於SQL Server實例下所有庫
for CREATE_PROCEDURE
as 
  --獲取事件數據
  DECLARE @data XML
  SET @data = EVENTDATA()

  declare @dbName nvarchar(50)
  declare @ProcName nvarchar(150)
  declare @ProcSQL nvarchar(max)

  --獲取新建存儲過程的資料庫名
  SET @dbName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
  --獲取新建存儲過程的名字
  set @ProcName= @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
  --獲取新建存儲過程的內容
  set @ProcSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'sysname')

  --將資料庫名、存儲過程名以及存儲過程內容插入ProcSqlTable表
  insert into [master].[dbo].[ProcSqlTable]([dbName],[ProcName],[ProcSQL]) 
  values(@dbName,@ProcName,@ProcSQL)

GO

ENABLE TRIGGER [tr_saveProcSql] ON ALL SERVER
GO

USE [master]
GO
create trigger [tr_saveBackProcSql]
on all server  --作用於SQL Server實例下所有庫
for ALTER_PROCEDURE
as 
  --獲取事件數據
  DECLARE @data XML
  SET @data = EVENTDATA()

  declare @dbName nvarchar(50)
  declare @ProcName nvarchar(150)
  declare @LoginName nvarchar(150)
  declare @ProcSQL nvarchar(max) --存儲過程內容
  declare @OldProcSQL nvarchar(max)--修改前的存儲過程內容

  --獲取修改存儲過程的資料庫名
  SET @dbName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
  --獲取修改存儲過程的名字
  set @ProcName= @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
  --獲取修改存儲過程的內容
  set @ProcSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'sysname')
  --獲取用戶名
  set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

  if exists(select 1 from [master].[dbo].[ProcSqlTable] where [dbName]=@dbName and [ProcName]=@ProcName)
    begin 
        --如果系統里有該存儲過程的記錄，獲取修改前的存儲過程內容
        select @OldProcSQL=[ProcSQL]
        from [master].[dbo].[ProcSqlTable]
        where [dbName]=@dbName and [ProcName]=@ProcName
    end
  else
    begin
         --如果沒有將資料庫名、存儲過程名以及存儲過程內容插入ProcSqlTable表
         insert into [master].[dbo].[ProcSqlTable]([dbName],[ProcName],[ProcSQL]) 
         values(@dbName,@ProcName,@ProcSQL)

         --退出
         return
    end

  --更新ProcSqlTable表存儲過程的內容供下次使用
  update [master].[dbo].[ProcSqlTable]
  set [ProcSQL]=@ProcSQL
  where [dbName]=@dbName and [ProcName]=@ProcName 

  --將資料庫名、存儲過程名以及修改前的存儲過程內容插入BackProcSqlTable表
  insert into [master].[dbo].[BackProcSqlTable]([dbName],[ProcName],[ProcSQL],[AlterDate],[AlterUser]) 
  values(@dbName,@ProcName,@OldProcSQL,GETDATE(),@LoginName)
  
GO

ENABLE TRIGGER [tr_saveBackProcSql] ON ALL SERVER
GO

=====================================================================================
本文只代表本人的見解，可能存在錯誤，僅用於技術交流。如果你喜歡該文，可以掃下麵的二維碼打賞我（打賞敬請備註“博客園打賞”五字）。