New 2005 Feature: DDL triggers

As a DBA, have you ever wanted to keep an eye on what is being created and deleted from the structure of your database?  In 2000 you could use the profiler subsystem to keep up with the changes, but in 2005, with the implementation of DDL triggers, it will be an easy task.  In the following sample code, I will build a DDL trigger that will log all create, alter and drop table commands executed in the database:

--first create a table to log to
CREATE TABLE dbo.tableChangeLog
(
    tableChangeLogId INT IDENTITY
                CONSTRAINT pkTableChangeLog PRIMARY KEY (tableChangeLogId),
    dateOfChange    datetime,
    ddl  varchar(max)--so we can get as much of the batch as possible
)
GO

--very simple architecture
CREATE TRIGGER tr_server$allTableDDL
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
 BEGIN
   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
        --we get our data from the EVENT_INSTANCE XML stream
        INSERT INTO dbo.tableChangeLog (dateOfChange,ddl)
        SELECT getdate(),
                 EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                                                                         'nvarchar(max)')
   END TRY
   BEGIN CATCH
             IF @@trancount > 0
                ROLLBACK TRANSACTION

              DECLARE @ERROR_MESSAGE varchar(8000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END

go

Now we create a table, alter the table, and then drop the table:

create table dbo.test
(
    id int
)
go
alter table dbo.test
   add newColumn int null

drop table dbo.test
go

Then you can look at the data:

select * from dbo.tableChangeLog

And you can see the changes:

tableChangeLogId   dateOfChange         ddl
----------------  ----------------------- ----------------------------------------------------------

1                2005-05-26 20:41:53.100  create table dbo.test
                                                          (
                                                                 id int
                                                           )
2                2005-05-26 20:41:53.220   alter table dbo.test
                                                                     add newColumn int null
3                2005-05-26 20:41:53.310   drop table dbo.test


This is really cool stuff, probably not all that exciting for the production database system, though it is something I can forsee applying to the database server just to log any changes to tables that are made during times other than normal code rollout periods. 

DDL triggers will also be helpful in development systems to help you keep up with what changes have been mad to the development databases during "rapid" development. All in all a great feature.