CREATE TABLE [dbo].[administratorlog](
	[idkey] [int] IDENTITY(1,1) NOT NULL,
	[databasename] [nvarchar](256) NULL,
	[eventtype] [nvarchar](50) NULL,
	[objectname] [nvarchar](256) NULL,
	[objecttype] [nvarchar](25) NULL,
	[sqlcommand] [nvarchar](max) NULL,
	[loginname] [nvarchar](256) NULL,
 CONSTRAINT [PK_administratorlog] PRIMARY KEY CLUSTERED 
(
	[idkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TRIGGER [Admin_Backup_Objects]
ON DATABASE

FOR create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function

AS

SET NOCOUNT ON

DECLARE @data XML

SET @data = EVENTDATA()

INSERT INTO dbo.AdministratorLog(
	databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES(
	@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
	@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),  -- value is case-sensitive
	@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
	@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
	@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
	@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'))
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [Admin_Backup_Objects] ON DATABASE
GO

use master
CREATE TABLE [dbo].[DDLEVENTLOG] ( 
[DATABASENAME]      SYSNAME                          NOT NULL,
[EVENTDATE]         DATETIME                             NULL,
[USERNAME]          SYSNAME                              NULL DEFAULT (getdate()),
[SYSTEMUSER]        VARCHAR(128)                         NULL,
[CURRENTUSER]       VARCHAR(128)                         NULL,
[ORIGINALUSER]      VARCHAR(128)                         NULL,
[HOSTNAME]          VARCHAR(128)                         NULL,
[APPLICATIONNAME]   VARCHAR(128)                         NULL,
[SCHEMANAME]        SYSNAME                              NULL,
[OBJECTNAME]        SYSNAME                              NULL,
[OBJECTTYPE]        SYSNAME                              NULL,
[EVENTTYPE]         VARCHAR(128)                         NULL,
[EVENTDATA]         XML                                  NULL,
[COMMANDTEXT]       VARCHAR(max)                         NULL,
[OBJECTDEFINITION]  VARCHAR(max)                         NULL)
GO
use SandBox
GO
ALTER TRIGGER [ReturnPREventData]
ON DATABASE
  WITH EXECUTE AS 'dbo'
FOR 
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,
CREATE_FUNCTION,  DROP_FUNCTION,  ALTER_FUNCTION,
CREATE_VIEW,      DROP_VIEW,      ALTER_VIEW,
CREATE_TABLE,     DROP_TABLE,     ALTER_TABLE,
CREATE_TRIGGER,   DROP_TRIGGER,   ALTER_TRIGGER
AS 
BEGIN
  SET NOCOUNT ON 
  declare @ObjectDef table(definition varchar(max))
  declare 
    @eventData          XML,
    @DATABASENAME      SYSNAME,
    @EVENTDATE         DATETIME,
    @USERNAME          SYSNAME,
    @SYSTEMUSER        VARCHAR(128),
    @CURRENTUSER       VARCHAR(128),
    @ORIGINALUSER      VARCHAR(128),
    @HOSTNAME          VARCHAR(128),
    @APPLICATIONNAME   VARCHAR(128),
    @SCHEMANAME        SYSNAME,
    @OBJECTNAME        SYSNAME,
    @OBJECTTYPE        SYSNAME,
    @EVENTTYPE         VARCHAR(128),
    @COMMANDTEXT       VARCHAR(max),
    @NAMEFORDEFINITION VARCHAR(261)

  --Load Variables from the xml
  SET @eventData = eventdata()
  SELECT
    @DATABASENAME    = db_name(),
    @EVENTDATE       = GETDATE(),
    @USERNAME        = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
    @SYSTEMUSER      = SUSER_SNAME(), 
    @CURRENTUSER     = CURRENT_USER, 
    @ORIGINALUSER    = ORIGINAL_LOGIN(),
    @HOSTNAME        = HOST_NAME(),
    @APPLICATIONNAME = APP_NAME(),
    @SCHEMANAME      = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
    @OBJECTNAME      = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
    @OBJECTTYPE      = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
    @COMMANDTEXT     = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
    @EVENTTYPE       = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
  --get the object definition
  SET @NAMEFORDEFINITION = '[' + @SCHEMANAME + '].[' + @OBJECTNAME + ']'
  INSERT INTO @ObjectDef(definition)
    EXEC sp_getDDL @NAMEFORDEFINITION
    --now save the audit info
  INSERT [master].[dbo].[DDLEventLog] (DATABASENAME,EVENTDATE,USERNAME,SYSTEMUSER,CURRENTUSER,ORIGINALUSER,HOSTNAME,APPLICATIONNAME,SCHEMANAME,OBJECTNAME,OBJECTTYPE,EVENTTYPE,EVENTDATA,COMMANDTEXT,OBJECTDEFINITION) 
    SELECT
      @DATABASENAME,
      @EVENTDATE,
      @USERNAME,
      @SYSTEMUSER,
      @CURRENTUSER,
      @ORIGINALUSER,
      @HOSTNAME,
      @APPLICATIONNAME,
      @SCHEMANAME,
      @OBJECTNAME,
      @OBJECTTYPE,
      @EVENTTYPE,
      @eventData,
      @COMMANDTEXT,
      definition 
    FROM  @ObjectDef
END --DB TRIGGER
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE 
GO