如何捕获DDL记录

简单来说有两种方法：

捕获 ddl 记录比较简便的有两种方法：

1. 用 SQL Profile 建立跟踪， 将日志存储到 master 库的表中;

优点：不需要创建对象， 操作简单;

缺点：影响性能，必须长期开着跟踪;

2. 创建数据库级别的 ddl 触发器;

优点：基本不影响性能;

缺点：无

方法一、创建跟踪

测试脚本：

USE tempdb

GO

IF OBJECT_ID('dbo.Proc_Test') IS NOT NULL

DROP PROC dbo.Proc_Test

GO

CREATE PROC dbo.Proc_Test

AS

BEGIN

SET NOCOUNT ON

SELECT 1

END

GO

DROP PROC dbo.Proc_Test

GO

SELECT * FROM MASTER.dbo.sqlProfile AS sp WHERE sp.TextData LIKE '%proc%'

--实际效果图

方法二、创建 ddl 触发器

--本demo仅为演示， 所以建立在 tempdb 库， 如需要在实际库上用， 可以将 tempdb 全部替换成 实际库的名称

--在 tempdb 上创建 ddl 触发器

USE tempdb;

GO

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'DDL_DATABASE_LEVEL_EVENTS')

DROP TRIGGER [DTG_DatabaseDdlTriggerLog] ON DATABASE;

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>' ;

--以下为发邮件内容，必须先配置 sql 邮件。暂时注释

--    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

--2. 创建数据库日志表

USE msdb;

GO

IF OBJECT_ID('[dbo].[DatabaseLog]') IS NOT NULL

DROP TABLE [dbo].[DatabaseLog]

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

--3. tempdb 上测试

USE tempdb

GO

IF OBJECT_ID('dbo.Proc_tempdb') IS NOT NULL

DROP PROC dbo.Proc_tempdb

GO

CREATE PROC dbo.Proc_tempdb

AS

BEGIN

SET NOCOUNT ON

SELECT 1

END

GO

DROP PROC dbo.Proc_tempdb

GO

SELECT * FROM msdb.dbo.[DatabaseLog]

--结果如下