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

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