简单来说有两种方法:
捕获 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]
--结果如下