Tracking code and code versions (source control) are a very essential part of any software company and there are lots of tools that do this varying from simpler ones such as SVN to more complex such as GIT to better integrate in VS such as TFS.
However, tracking DB objects has proven to be a little more difficult due to the nature of their storage. They are not stored on the file system but inside the database. There are actually few tools that manage DB objects versions but are either too complex for a simple job of tracking, are quite expensive or require lots of new steps to be done by the developers which then gets met with change resistance issues.
Red Gate’s SQL Source Control tool is quite good but as I mentioned earlier, it is too expensive, way more complicated than we need and requires several extra steps from each developer. Plus some tools require each developer to have his/her own version of the database locally which is usually unwanted.
I have researched this topic and found a simple way of tracking DDL updates on SQL Server and storing these changes to allow developers or DB admins to manage objects’ change history.
My solution consists of two parts: tables and a trigger.
- Main Table that stores information about the updated object and the event type that triggered the change
- Secondary Table that stores the text content (body) of the object such as the SP or function body.
- A database-level trigger that get triggered on certain events that are specified by the developer such as
- This trigger saves the event and the updated object’s information into the above two tables.
As simple as that.
Using the Code
The following script block creates the main table (
CREATE TABLE [dbo].[versioning_DBObjectsVersions]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](255) NOT NULL, [Type] [varchar](255) NOT NULL, [EventType] [varchar](255) NULL, [Schema] [varchar](255) NULL, [DateModified] [datetime] NOT NULL, [Client] [varchar](255) NULL, CONSTRAINT [PK_DBObjectsVersions] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _ IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];
The following script creates the secondary table (versioning_DBObjectsVersionsContent):
CREATE TABLE [dbo].[versioning_DBObjectsVersionsContent]( [ID] [int] IDENTITY(1,1) NOT NULL, [DBObjectVersionsID] [int] NOT NULL, [Text] [nvarchar](max) NOT NULL, CONSTRAINT [PK_DBObjectsVersionsContent] PRIMARY KEY CLUSTERED ([ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _ IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];
Finally references between the tables are added:
ALTER TABLE [dbo].[versioning_DBObjectsVersionsContent] WITH CHECK ADD
_CONSTRAINT [FK_DBObjectsVersionsContent_DBObjectsVersions] FOREIGN KEY([DBObjectVersionsID])
REFERENCES [dbo].[versioning_DBObjectsVersions] ([ID]);
ALTER TABLE [dbo].[versioning_DBObjectsVersionsContent] _
CHECK CONSTRAINT [FK_DBObjectsVersionsContent_DBObjectsVersions];
The above two tables will be used to store all the needed information about each DDL event.
ID int– auto incrementing integer that will be used as the revision #
Name varchar(255)– name of the object such as a function name, table name, etc.
Type varchar(255)– type of the object such as
EventType varchar(255)– type of the event that triggered the database trigger such as
Schema varchar(255)– schema of the object such as dbo
DateModified datetime– date and time of the event
Client varchar(255)– IP-Address or name of the machine from which the event was triggered
Text nvarchar(MAX)– the body/command of the object such as the function body, procedure body, DDL statement that updated a table, etc.
The first section is defining the database trigger with the DDL Event Groups.
CREATE TRIGGER [versioning_CaptureObjectsChanges] ON DATABASE
FOR CREATE_PROCEDURE , ALTER_PROCEDURE , DROP_PROCEDURE ,
CREATE_FUNCTION , ALTER_FUNCTION , DROP_FUNCTION ,
CREATE_TRIGGER , ALTER_TRIGGER , DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
The above trigger definition is defined to be triggered on any DDL changes on any Table, Procedure, Function or View. You can remove or add more DDL Event Groups depending on your needs. You can find more description and the rest of the Event Groups in this link.
The following trigger part captures all the necessary information about the event, DB object updated and the Client’s IP Address.
SET NOCOUNT ON;
DECLARE @EventData xml
, @ip varchar( 32 );
SELECT @EventData = EVENTDATA( );
SELECT @ip = client_net_address
WHERE session_id = @@SPID;
DECLARE @ObjectName nvarchar( 255 )
DECLARE @ObjectType nvarchar( 255 )
DECLARE @EventType nvarchar( 255 )
DECLARE @SchemaName nvarchar( 255 )
DECLARE @Content nvarchar( max )
SELECT @ObjectName = @EventData.value_
( '(/EVENT_INSTANCE/ObjectName)' , 'NVARCHAR(255)' ) ,
@ObjectType = @EventData.value_
( '(/EVENT_INSTANCE/ObjectType)' , 'NVARCHAR(255)' ) ,
@EventType = @EventData.value_
( '(/EVENT_INSTANCE/EventType)' , 'NVARCHAR(255)' ) ,
@SchemaName = @EventData.value_
( '(/EVENT_INSTANCE/SchemaName)' , 'NVARCHAR(255)' )
SELECT @Content = @EventData.value_
( '(/EVENT_INSTANCE/TSQLCommand)' , 'NVARCHAR(MAX)' )
The following trigger part retrieves the latest version of the updated object stored in the tables (if exists) and compares the old content with the new just updated content. If they are the same, ignore the event (many developers have a habit of executing a procedure, for example, several times in a row).
Finally, if they are different, save the main information in the main table and the body of the updated object in the secondary table:
DECLARE @LatestVersionBody nvarchar( max )
SELECT TOP 1 @LatestVersionBody = Text
FROM versioning_DBObjectsVersions main
JOIN versioning_DBObjectsVersionsContent body ON main.ID = body.DBObjectVersionsID
WHERE main.Name = @ObjectName
AND main.Type = @ObjectType
AND main.[Schema] = @SchemaName
ORDER BY main.ID DESC
IF(@LatestVersionBody <> @Content)
INSERT INTO versioning_DBObjectsVersions( Name ,
SELECT @ObjectName ,
GETDATE( ) ,
DECLARE @newID int
SELECT @newID = SCOPE_IDENTITY( )
INSERT INTO versioning_DBObjectsVersionsContent( DBObjectVersionsID ,
SELECT @newID ,
This trigger in conclusion does the following:
- Retrieves the information needed from the
EventData()object plus it obtains the client’s IP address
- Gets the latest version of the updated object stored in the tables (if exists) and compares the old content with the new just updated content, If they are the same, ignore the event (many developers have a habit of executing a procedure, for example, several times in a row)
- Save the main information in the main table and the body of the updated object in the secondary table
After the trigger has been created, make sure it is enabled at all times to keep capturing events. The following script enables it:
ENABLE TRIGGER [versioning_CaptureObjectsChanges] ON DATABASE
At this point, any DDL change on the set of objects that you specified in the trigger definition should add a new record in each table, and that’s it.
The data stored in the tables are really useful and have lots of uses if used properly. One way for me was to develop a tool that reads from these tables to allow advanced searching, versioning, comparing, listing, creating migration scripts, etc..
This tool is a Windows-Forms desktop application developed using .NET 4.0.
If you are interested in this tool, you can find it in the link at the top of this post.
Note: The DB scripts described above are available with the tool, and the tool itself can execute them without you worrying about them.
- 2015-11-10: First draft v1.0 (tool attached: v1.0 [BETA])
- 2015-11-25: Tool Updated v1.1
- Bug Fixes
- Add Checkboxes to grid
- Add ‘Revert To Rev’ option
- 2015-12-07: Tool Updated v1.2
- Bug Fixes
- Add ‘Load Release Notes to get latest’ which allows loading of previously saved release notes in order to get the objects’ latest versions (working copy) from the DB