« Search Stored Procedures
SVN Port TCP Port is 3690 »


SQL Server Schema,Table,Function and Stored procedure Auditing

by Jon Hibbins on 17 December 2008
Filed under: Code, MSSQL2005, MSSQL2008, Objective-C, SQL Server, Software Development, TSQL, iPhone

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

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “SQL Server Schema,Table,Function and Stored procedure Auditing”

  1. SQL Tutorials Says:
    May 1st, 2009 at 2:35 am

    Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

Leave a Reply