监控sqlserver2005 DDL操作

CREATE TABLE dbo.DDLEventLog
(EventID INT PRIMARY KEY IDENTITY,
EventInstance XML NOT NULL)
GO

CREATE TRIGGER DDLLogEvents
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO dbo.DDLEventLog (EventInstance)
VALUES (EVENTDATA())

-------------------------------------------------------------------------------------------------------------

create view DDL_monitor as select EventID,EventInstance.value('(//LoginName)[1]',? 'nvarchar(30)') as 登录名,EventInstance.value('(//DatabaseName)[1]',? 'nvarchar(30)') as 数据库,EventInstance.value('(//CommandText)[1]',? 'nvarchar(400)') as DDL语句,replace(EventInstance.value('(//PostTime)[1]',? 'nvarchar(300)'),'T',' ') as 执行时间 ?from lijiwei002.dbo.DDLEventLog union select EventID,' ') as 执行时间 ?from lijiwei003.dbo.DDLEventLog union select EventID,' ') as 执行时间 ?from test002.dbo.DDLEventLog

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!