Trigger to capture schema changes in the Server

Question

Is it possible to implement something like the following trigger

CREATE TRIGGER [tr_AU_ddl_All_Server] ON DATABASE
    WITH EXECUTE AS self
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE
        @data XML
      , @rc INT
    SET @data = EVENTDATA()
    EXEC @rc = __AU.dbo.AU_DDLLog @data
GO

BUT on the whole server. My idea is to capture all schema changes across all the databases in the server.

As far as im concerned this is not possible in SQL Server 2005, but I'd like to know if anyone got something like this to work. I'd like to avoid having to implement a trigger in every single database.

Answer 1

Yes, SQL Server 2005 introducted the "DDL Triggers" - read an excellent article on it here at SQL Team.

This article shows nicely that they are two scopes for DDL triggers - server-wide, or database-wide. Those that are database-wide cannot be applied to the whole server - you'd have to set them up in each database.

Marc