Simple SQL Source Control using DDL triggers

In the company I work for, we use SQL Servers with a lot of business code in stored procedures and functions. With growing complexity and multiple developers modifying the same code the need for source control has risen.
Unfortunately source control for SQL servers is not provided in a useful way out-of-the-box. TFS integration in SQL Management Studio uses local file which is a very unconvenient way. 3rd party solutions, like RedGate, are not fully intergated with the server, so it is possible to make changes without interaction of the source control...
We wanted a solution that enabled monitoring of every change on the database, regardless of the user having installed the product or not, and which does not require any overhead (such as the need to click on 5 screens just to change a simple procedure).

So we were looking for some other ways. After a little research we found this article, which suggested using DDL triggers. And that seemed to be the solution. Based on the article we created our own simple SQL Source Control system.

The solution

We added a separate Source Control Database named SourceControlDB on the SQL server. It contains a simple table (log) which is filled with database-change-logs.

CREATE TABLE [log]
(
    [PostTime] [datetime],
    [ServerName] [nvarchar](256),
    [DataBaseName] [nvarchar](256) ,
    [Event] [nvarchar](256) ,
    [ObjectName] [nvarchar](256) ,
    [TSQL] [nvarchar](MAX) ,
    [Login] [nvarchar](256)
)

On databases we want to monitor, we add a DDL trigger like this:

CREATE TRIGGER [SourceControlTrigger]
ON DATABASE
FOR DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TRIGGER_EVENTS
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO SourceControlDB.dbo.log (PostTime, ServerName, DatabaseName, Event, ObjectName, TSQL, Login)
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)')
)

Now every change on that database is logged to the log table of the SourceControlDB database:

E.g.:

PostTime: 2011-08-19 13:17:38
ServerName: MyServer1
DatabaseName: MyDatabase
EventType: CREATE_PROCEDURE
ObjectName: sp_testLog
TSQLCommand: CREATE PROCEDURE sp_testLog AS SELECT 'Log'
LoginName: Company\User