SQL Server Schema

SQL Server Schema,Table,Function and Stored procedure Auditing

By | Code, iPhone, MSSQL2005, MSSQL2008, Objective-C, Software Development, SQL Server, TSQL | One Comment

If you like to know what’s changing on your SQL Server then the following code creates an audit table and the associated trigger for logging the changes

The Audit Object Table

CREATE TABLE [dbo].[AuditObjects](
 [EventID] [INT] IDENTITY(1,1) NOT NULL
,[EventData] [xml] NULL
PRIMARY KEY CLUSTERED (
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the associated Trigger

CREATE TRIGGER [TriggerAuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO [dbo].[AuditObjects](EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [TriggerAuditObjects] ON DATABASE

You can now use xpath queries to analyse the data