posted on Tuesday, October 02, 2007 2:24 AM by thomasswilliams

Auditing Schema Changes to SQL Server 2005 objects

I recently needed to implement a lightweight, simple audit trail in SQL Server 2005. After googling and finding many, many ways, I settled on Richard's recent blog post over at GeekDojo titled "Super easy SQL Server 2005 Database Schema change auditing".

Richard has posted a short, helpful script to audit data definition (CREATE, DROP, ALTER) SQL statements to a central table using a simple trigger and the XML data type. This means I can keep an audit trail of schema changes, along with all the information available (user name, date & time, SQL statement, etc.)

I'm looking forward to working with this. Any advice for pulling data out of the XML data type?

Tags: sql server, audit, schema

Comments

# Interesting Finds: October 2, 2007 @ Tuesday, October 02, 2007 10:04 AM

Anonymous

# Interesting Finds: October 2, 2007 @ Tuesday, October 02, 2007 10:07 AM

Anonymous

# re: Auditing Schema Changes to SQL Server 2005 objects @ Tuesday, October 02, 2007 4:31 PM

Thomas,
Here is an example of querying XML data stored in a table.

IF OBJECT_ID('XMLTABLE', 'U') IS NOT NULL
DROP TABLE DBO.XMLTABLE
GO
CREATE TABLE DBO.XMLTABLE
(
ID INT NOT NULL
,XMLCOLUMN XML
)
GO

INSERT DBO.XMLTABLE (ID, XMLCOLUMN) VALUES (1, '<CUSTOMERS>
<CUSTOMER CUSTOMERID="1" CUSTOMERNAME="CHARLES SCHWAB">
<ACCOUNTS>
<ACCOUNT ACCOUNTID="1" ACCOUNTNAME="IMAGE CONSULTANT" />
<ACCOUNT ACCOUNTID="501" ACCOUNTNAME="IMAGE CONSULTANT SOUTH" />
</ACCOUNTS>
</CUSTOMER>
</CUSTOMERS>')
INSERT DBO.XMLTABLE (ID, XMLCOLUMN) VALUES (2, '<CUSTOMERS>
<CUSTOMER CUSTOMERID="2" CUSTOMERNAME="MOHAWK INDUSTRIES INC.">
<ACCOUNTS>
<ACCOUNT ACCOUNTID="2" ACCOUNTNAME="GEOLOGICAL ENGINEER" />
</ACCOUNTS>
</CUSTOMER>
</CUSTOMERS>')

SELECT
XMLCOLUMN.VALUE('(/CUSTOMERS/CUSTOMER/@CUSTOMERID) [1]', 'INT') [CUSTOMERID]
,TABLENAME.COLUMNNAME.VALUE('@ACCOUNTID', 'INT') AS ACCOUNTID
,TABLENAME.COLUMNNAME.VALUE('@ACCOUNTNAME', 'VARCHAR(100)') AS ACCOUNTNAME
FROM
XMLTABLE
CROSS APPLY XMLCOLUMN.NODES('/CUSTOMERS/CUSTOMER/ACCOUNTS/ACCOUNT') AS TABLENAME(COLUMNNAME)

The cross apply is done so that you may query multiple rows. If you are only interested in a specific row you could use the .nodes option with a row specified. See BOL for more on that. :)

Hope this helps...

Whitney

# re: Auditing Schema Changes to SQL Server 2005 objects @ Tuesday, October 09, 2007 10:17 PM

Thanks for the pointer Whitney - I took your code and mashed it up with BOL, to get a working example (in another blog post).

Cheers, Thomas

thomasswilliams

# Reporting Audit Changes to SQL Server 2005 objects @ Tuesday, October 09, 2007 10:48 PM

Following on from my last post on &quot;Auditing Schema Changes to SQL Server 2005 objects&quot;, here's a simple...

Anonymous

# OT: Merry Christmas and Blog Stats 2007 Edition @ Monday, December 17, 2007 9:53 PM

Merry Christmas to all my reader(s). I hope you have a great Christmas and New Year, and I’ll see you...

Anonymous