Maintain SQL Server 2005 DB changes in log table
EVENTDATA() is a function is provided by SQL Server 2005, It provides Information about an event that fires a DDL trigger is captured by using the EVENTDATA function. This function returns an xml value. The XML schema includes information about the following:
· The time of the event.
· The System Process ID (SPID) of the connection when the trigger executed.
· The type of event that fired the trigger. Steps for implementation of storing DDL log changes in Table
1. Create table to store DDL Transactions
CREATE TABLE [dbo].[DDLLog](
[logId] [int] IDENTITY(1,1) NOT NULL,
[logData] [xml] NOT NULL,
CONSTRAINT [PK_DDLLog] PRIMARY KEY CLUSTERED
([logId] ASC)) ON [PRIMARY]
2. Create Trigger to fetch DDL commands info and store in DDLLog table
CREATE TRIGGER [Trig_DDLLog]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO dbo.DDLLog(logData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [Trig_DDLLog] ON DATABASE
3. Create a test table to check
create table testTable (id int, description varchar(100))
4. Watch DDLLog table for log entry …it is available in XML format like below.
<EVENT_INSTANCE>
<EventType>CREATE_TABLEEventType>
<PostTime>2008-04-10T11:48:08.463PostTime>
<SPID>53SPID>
<ServerName>BSIAP021ServerName>
<LoginName>saLoginName>
<UserName>dboUserName>
<DatabaseName>AdventureWorksDatabaseName>
<SchemaName>dboSchemaName>
<ObjectName>testTableObjectName>
<ObjectType>TABLEObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS=“ON” ANSI_NULL_DEFAULT=“ON”
ANSI_PADDING=“ON” QUOTED_IDENTIFIER=“ON” ENCRYPTED=“FALSE”
/>
<CommandText>create table testTable (id int, description varchar(100))
CommandText>
TSQLCommand>
EVENT_INSTANCE>
References: http://technet.microsoft.com/en-us/library/ms187909.aspx
Now it is so simple to maintain SQL Server DDL changes log
