监控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,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 lijiwei003.dbo.DDLEventLog
union
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 test002.dbo.DDLEventLog