Tracking SQL-Server DB Objects Versions

• Download Tool - 1.5 MB

Introduction

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 integrated 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.

Background

My solution consists of two parts: tables and a trigger.

1. Tables

• 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.

2. Trigger

• A database-level trigger that get triggered on certain events that are specified by the developer such as CREATE_PROCEDURE, ALTER_FUNCTION, DROP_VIEW, etc.
• This trigger saves the event and the updated object's information into the above two tables.

As simple as that.

Using the Code

Tables Script

The following script block creates the main table (versioning_DBObjectsVersions):

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.

1. ID int - auto incrementing integer that will be used as the revision #
2. Name varchar(255) - name of the object such as a function name, table name, etc.
3. Type varchar(255) - type of the object such as PROCEDURE, FUNCTION, TRIGGER, TABLE, etc.
4. EventType varchar(255) - type of the event that triggered the database trigger such as CREATE_FUNCTION, ALTER_TRIGGER, DROP_TABLE, etc.
5. Schema varchar(255) - schema of the object such as dbo
6. DateModified datetime - date and time of the event
7. Client varchar(255) - IP-Address or name of the machine from which the event was triggered
8. Text nvarchar(MAX) - the body/command of the object such as the function body, procedure body, DDL statement that updated a table, etc.

Trigger Script

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
AS
BEGIN

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
     FROM sys.dm_exec_connections
     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)[1]' , 'NVARCHAR(255)' ) ,
          @ObjectType = @EventData.value_
          ( '(/EVENT_INSTANCE/ObjectType)[1]' , 'NVARCHAR(255)' ) ,
          @EventType = @EventData.value_
          ( '(/EVENT_INSTANCE/EventType)[1]' , 'NVARCHAR(255)' ) ,
          @SchemaName = @EventData.value_
          ( '(/EVENT_INSTANCE/SchemaName)[1]' , 'NVARCHAR(255)' )

   SELECT @Content = @EventData.value_
   ( '(/EVENT_INSTANCE/TSQLCommand)[1]' , '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)
        BEGIN
            INSERT INTO versioning_DBObjectsVersions( Name ,
                                                      Type ,
                                                      EventType ,
                                                      [Schema] ,
                                                      DateModified ,
                                                      Client )
            SELECT @ObjectName ,
                   @ObjectType ,
                   @EventType ,
                   @SchemaName ,
                   GETDATE( ) ,
                   @ip

            DECLARE @newID int
            SELECT @newID = SCOPE_IDENTITY( )

            INSERT INTO versioning_DBObjectsVersionsContent( DBObjectVersionsID ,
                                                             Text )
            SELECT @newID ,
                   @Content
        END

END      

This trigger in conclusion does the following:

1. Retrieves the information needed from the EventData() object plus it obtains the client's IP address
2. 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)
3. 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.

Points of Interest

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.

History

• 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