--//
CREATE
go
USE
--
CREATE
DDLHistory
(
[Login]
NVARCHAR(100),
[Action]
NVARCHAR(100),
[Schema]
NVARCHAR(100 ),Object
TSQL
)
go
--
CREATE
ON
DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @data XML
SET @data = eventdata ()
INSERT DDLHistory ([Time], [Login], [Action], [Schema], [Object], TSQL)
VALUES
(GETDATE(), @data.value('data(//LoginName)[1]', 'NVARCHAR(2000)'),
@data
@data.value('data(//SchemaName)[1]', 'NVARCHAR(2000)' ),
.value('data(//EventType)[1]', 'NVARCHAR(2000)'),@data
@data
END
go
--
CREATE
TestTable
(
)
go
ALTER
ADD
go
CREATE
AS
SELECT * FROM TestTable
go
CREATE
AS
SELECT * FROM TestTable
go
ALTER
AS
SELECT
go
ALTER
AS
SELECT
END
go
--
SELECT
FROM DDLHistory
use
go
--
---drop database Testdb
master ;[Time], [Action], [Schema], [Object], TSQL BEGINB FROM TestTablePROC prcTestViewa FROM TestTable VIEW vwTestViewPROC prcTestViewVIEW vwTestViewB INTTABLE TestTableA INT TABLE .value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)') ).value('data(//ObjectName)[1]', 'NVARCHAR(2000)' ), TRIGGER trDDLHistoryRecorder NVARCHAR ( 2000 ) NVARCHAR ( 100 ), [Time] DATETIME , TABLE Testdb DATABASE Testdb ;