You have to love the xml type in SQL Server 2005. Here's a simple way I found to make use of it: You can audit all the object/schema changes to the database with a simple database-level trigger.
First, create a very simple table (inside a schema I name 'Audit'):
CREATE TABLE [Audit].[Objects](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Then, the trigger:
CREATE TRIGGER [Trig_AuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO Audit.Objects(EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [Trig_AuditObjects] ON DATABASE
That's it.. now get a nice neat little xml entry in my table every time a DDL database level event happens;
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2008-05-01T18:06:01.722</PostTime>
<SPID>55</SPID>
<ServerName>TestServ</ServerName>
<LoginName>domain\username</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Test2</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Table1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>ALTER TABLE dbo.Table1
DROP COLUMN testremove
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
The EVENTDATA() function is provided by SQL Server inside a DDL trigger and provides all the data you see above as an xml document.
Having this during development is like a poor man's source control for schema changes. It could come in very handy for forensic purposes when diagnosing post-rollout issues or accidental schema changes.
I wish I could take credit for developing this cool little find. I found it surfing some time ago. I copied and never got around to testing it. I was pretty happy when I did. Thanks to the unknown coder of this one!