Script to create audit tables and DDL Triggers

USE [Test]




GO




/****** Object: Table [dbo].[audit_table_database_functions] Script Date: 2/6/2014 5:24:15 PM ******/

CREATE TABLE [dbo].[audit_table_database_functions](

[funtion_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




GO




/****** Object: Table [dbo].[audit_table_database_procedures] Script Date: 2/6/2014 5:24:31 PM ******/

CREATE TABLE [dbo].[audit_table_database_procedures](

[procedure_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



　
/****** Object: Table [dbo].[audit_table_database_tables] Script Date: 2/6/2014 5:24:40 PM ******/

CREATE TABLE [dbo].[audit_table_database_tables](

[table_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



　
/****** Object: Table [dbo].[audit_table_database_views] Script Date: 2/6/2014 5:24:49 PM ******/

CREATE TABLE [dbo].[audit_table_database_views](

[view_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




/*******************************Triggers******************************/

USE [Test]




GO




/****** Object: DdlTrigger [DDL_Funtion_Trigger] Script Date: 2/6/2014 5:25:07 PM ******/

CREATE TRIGGER [DDL_Funtion_Trigger]

ON DATABASE

FOR ALTER_FUNCTION, DROP_FUNCTION, CREATE_FUNCTION




AS
INSERT INTO audit_table_database_functions

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Funtion_Trigger] ON DATABASE




GO

USE [Test]




GO




/****** Object: DdlTrigger [DDL_Procedure_Trigger] Script Date: 2/6/2014 5:25:26 PM ******/

CREATE TRIGGER [DDL_Procedure_Trigger]

ON DATABASE

FOR ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_PROCEDURE




AS
INSERT INTO audit_table_database_procedures

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Procedure_Trigger] ON DATABASE




GO

USE [Test]




GO




/****** Object: DdlTrigger [DDL_View_Trigger] Script Date: 2/6/2014 5:25:37 PM ******/

CREATE TRIGGER [DDL_View_Trigger]

ON DATABASE

FOR ALTER_VIEW, DROP_VIEW, CREATE_VIEW




AS
INSERT INTO audit_table_database_views

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_View_Trigger] ON DATABASE




GO




/****** Object: DdlTrigger [DDL_Table_Trigger] Script Date: 2/6/2014 5:27:40 PM ******/

CREATE TRIGGER [DDL_Table_Trigger]

ON DATABASE

FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE




AS
INSERT INTO audit_table_database_tables

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Table_Trigger] ON DATABASE




GO