MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)

USE msdb;
GO
CREATE TABLE [dbo].[DatabaseLog]
(
    [DatabaseLogID]   [int]    IDENTITY(1,1) NOT NULL,
    [PostTime]        [datetime] NOT NULL,
    [DatabaseUser]    [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LoginName]       [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ClientHost]      [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Event]           [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Schema]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Object]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TSQL]            [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XmlEvent]        [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
    [DatabaseLogID] ASC
  )WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NPrimary key for DatabaseLog records. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NDatabaseLogID
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe date and time the DDL change occurred. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NPostTime
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe user who implemented the DDL change. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NDatabaseUser
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe login which implemented the DDL change. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NLoginName
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe client machine on which implemented the DDL change. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NClientHost
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe type of DDL statement that was executed. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NEvent
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe schema to which the changed object belongs. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NSchema
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe object that was changed by the DDL statment. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NObject
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe exact Transact-SQL statement that was executed. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NTSQL
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NThe raw XML data generated by database trigger. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCOLUMN,@level2name=NXmlEvent
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NAudit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog. , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog
GO
EXEC sys.sp_addextendedproperty@name=NMS_Description, @value=NPrimary key (nonclustered) constraint , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDatabaseLog, @level2type=NCONSTRAINT,@level2name=NPK_DatabaseLog_DatabaseLogID
GO

USE MyAssistant;
GO

CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @tableHTML  NVARCHAR(MAX) ;

    SET @data = EVENTDATA();
    SET @eventType = @data.value((/EVENT_INSTANCE/EventType)[1], sysname);
    SET @schema = @data.value((/EVENT_INSTANCE/SchemaName)[1], sysname);
    SET @object = @data.value((/EVENT_INSTANCE/ObjectName)[1], sysname)

    IF @object IS NOT NULL
        PRINT   + @eventType + - + @schema + . + @object;
    ELSE
        PRINT   + @eventType + - + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [msdb].[dbo].[DatabaseLog]
        (
        [PostTime],
        [DatabaseUser],
        [LoginName],
        [ClientHost],
        [Event],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent]
        )
    VALUES
        (
        GETDATE(),
        CONVERT(sysname, CURRENT_USER),
        @data.value((/EVENT_INSTANCE/LoginName)[1], nvarchar(max)),
        CONVERT(sysname, HOST_NAME()),
        @eventType,
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data.value((/EVENT_INSTANCE/TSQLCommand)[1], nvarchar(max)),
        @data
        );

    SET @tableHTML =  
    N<H1>DDL Event</H1> +    
    N<table border="0"> +    
    N<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th> +    
    N<th>TSQL</th><th></tr> +    
    CAST(( SELECT
    td = PostTime,       ,                    
    td = DatabaseUser, ,       
    td = LoginName, ,    
    td = ClientHost, ,        
    td = TSQL,               
    FROM msdb.dbo.DatabaseLog              
    WHERE DatabaseLogID =(select max(DatabaseLogID) from msdb.dbo.DatabaseLog)              
    FOR XML PATH(tr), TYPE     ) AS NVARCHAR(MAX) ) +     N</table> ;

    EXEC msdb.dbo.sp_send_dbmail    
             @profile_name = DataBase_DDL_Event,
        @recipients=***@***.com,    
        @subject = DDL Event - DataBase MyAssistant,    
        @body = @tableHTML,  
        @body_format = HTML ;
END;
GO