Here's the summary for it.

Over the years I have reviewed different source controls and have implemented TFS. I'm a DBA at heart it's not nice nice not being able to have a source control for database development especially that clients cannot afford the expensive ones.

I came upon a database trigger post which I modified to make a rough version control for our team.

It involves 2 things

1. Create a Trigger on the database where you want versioned

2. Create a database to save the versions of the things your working on. The DB need only contain 1 specific table.

How does it work

1. This tool saves every time you make changes on your objects -- assuming that the way you save your changes is by running it.

These include the following

- Views

- Tables

- Stored procs

- Functions

2. The database trigger event takes care of the saving. Everytime you press F5 on a alter proc, it will save a copy of your work on the target table which is preferably on another database on the save server.

3. To retrieve the data, you just need to query the data in the table.

The Database Trigger

CREATE TRIGGER [DDLTrigger_Sample]

ON DATABASE

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,ALTER_SCHEMA, ALTER_FUNCTION,CREATE_FUNCTION,DROP_FUNCTION,ALTER_VIEW,CREATE_VIEW,DROP_VIEW,RENAME,ALTER_TABle,CREATE_TABLE

AS

BEGIN

SET NOCOUNT ON;

DECLARE

@EventData XML = EVENTDATA();

DECLARE

@ip VARCHAR(32) =

(

SELECT client_net_address

FROM sys.dm_exec_connections

WHERE session_id = @@SPID

);

INSERT AuditDB.dbo.DDLEvents

(

EventType,

EventDDL,

EventXML,

DatabaseName,

SchemaName,

ObjectName,

HostName,

IPAddress,

ProgramName,

LoginName

)

SELECT

@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),

@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),

@EventData,

DB_NAME(),

@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),

@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),

HOST_NAME(),

@ip,

PROGRAM_NAME(),

SUSER_SNAME();

END

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDLTrigger_Sample] ON DATABASE

GO

The Table

CREATE TABLE [dbo].[DDLEvents](

[EventDate] [datetime] NOT NULL,

[EventType] [nvarchar](64) NULL,

[EventDDL] [nvarchar](max) NULL,

[EventXML] [xml] NULL,

[DatabaseName] [nvarchar](255) NULL,

[SchemaName] [nvarchar](255) NULL,

[ObjectName] [nvarchar](255) NULL,

[HostName] [varchar](64) NULL,

[IPAddress] [varchar](32) NULL,

[ProgramName] [nvarchar](255) NULL,

[LoginName] [nvarchar](255) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate]

If you find this interesting, I'd like to take a shot at an article for it. If not, nice to have posted here and shared :).