Light-weight T-SQL source code control

There are two types of SQL developers: one who has licence for Visual Studio with Team Foundation Server and the other has nothing. Why is this so?  This is another story. What should the "poor" developer do if he wants to develop (or study to develop) T-SQL stored procedures, create views etc. and he manage his own code.

For example: I have a small personal computer with SQL Server Express Edition and no Visual Studio installed. The licence costs nothing, this is good. There is no place and sometimes no money to buy and install also the Visual Studio, this is bad.

So, it is enough to use the Management Studio from SQL Server Express Edition. It is right, at the first time. After some months of studying, developing and testing the Transact-SQL code I have a lot of scripts, a lot of views. Why? I save each view, each stored procedure with a new name to leave the original one untouched for the sake of to compare the code later, find a mistake or use an old idea again.

It looks ugly. I hate this garbage of code. So, I need a source control management or something like that and for nothing.

 

Part 1. I am lazy and poor, but smart.

 

Each ALTER-command invokes an event in the database. I speak adhere and above about SQL 2008 Server. This event is an XML object and can be obtained with function call EVENTDATA()

DECLARE @event XML
SET @event = EVENTDATA()

The XML file is a unicode file and their schema can be found at here
The root-element is EVENT_INSTANCE and it becomes different type depending on command issued. For example: the command ALTER PROCEDURE notify the database with the corresponding event. This event presented as XML file has a root element of type EVENT_INSTANCE_ALTER_PROCEDURE. Some child elements of it need our attention: DatabaseName, SchemaName, ObjectName, ObjectType and TSQLCommand.

DatabaseName	string(128)	database name where command was issued
SchemaName	string(128)	"dbo"
ObjectName	string(128)	"myProcedure"
ObjectType	string(128)	"P"

The TSQLCommand element hat own complex type consisting on CommandText as a string. This string contains the new code for stored procedure and can be separate backed up. Such backups contain needed me history of changes. If I write such events or simply the command texts in a table with timestamp I get something like "light-weight" source code control for T-SQL scripts. I need no more any copies of scripts, any origins in my database. I have always a record with old version in the table.

But that is not all, folks!

 

Part 2: Catch me if you can.

 

How can I know when the event will be issued? How can I catch it?
The right and easiest way is to use a trigger. The trigger on database and not a table.

The complete code looks then like that:
CREATE TABLE [dbo].[ObjectHistory]
(
  [DatabaseName] [sysname] NOT NULL,
  [ChangeDate] [datetime] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [ObjectType] [nchar](1) NOT NULL,
  [ObjectValue] [nvarchar](max) NOT NULL,
  [UserName] [nvarchar](256) NULL,
  [Comment] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
  [DatabaseName] ASC,
  [ChangeDate] ASC,
  [ObjectName] ASC
))

GO
ALTER TABLE [dbo].[ObjectHistory] ADD CONSTRAINT [DF_ObjectHistory_DatabaseName] DEFAULT (db_name()) FOR [DatabaseName]
GO
ALTER TABLE [dbo].[ObjectHistory] ADD CONSTRAINT [DF_ObjectHistory_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate]
GO
ALTER TABLE [dbo].[ObjectHistory] ADD CONSTRAINT [DF_ObjectHistory_UserName] DEFAULT (user_name()) FOR [UserName]
GO
CREATE TRIGGER vccObjects
ON DATABASE
FOR ALTER_VIEW, ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_INDEX
AS
DECLARE @event XML
SET @event = EVENTDATA()
INSERT INTO DWH_ODS.dbo.ObjectHistory (ObjectName, ObjectType, ObjectValue)
VALUES (
  @event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
  @event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NCHAR(1)'),
  @event.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'))