Lightweight SQL source control using a single stored procedure

Ryan McCauleySenior Data Architect
CERTIFIED EXPERT
I'm a lover of data - management, querying, and presentation. I'm happy to help make things reliable, quick, and arranged to tell a story.
Published:
Updated:
INTRODUCTION:

While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a single stored procedure that periodically and automatically takes a snapshot of all your objects and archives them. At any point in time, you can see the history for a given database object, and it makes it trivial to restore a previous version of an altered or dropped stored procedure.

This is accomplished using a stored procedure (provided in full at the end of the article) that's created in the master database, and then scheduled to run at whatever interval you choose - anywhere from once/minute to once/week, or anywhere in between.

This Stored Procedure goes through the following steps:

1

If the Master.dbo.coSourceControl table (used to store the history) doesn't exist, it creates it

2

For each database on the server (so new databases are added automatically), it performs steps 3-5:

3

It grabs the text contents of all the user objects (not flagged as "IsMsShipped")

4

Compares the contents of each to the last known copy (if there is one)

5

If the object is new or has changed, add a new copy to the source control table in master

6

At the end, it will output the number of objects updated across all databases
The history is kept in a single table - master.dbo.coSourceControl - which has the database it came from, the object_id, the object name, object contents, and the timestamp. Since it uses the object_id to track things, it will also record a name change in an object, even if the contents didn't change, so you can see the history there.

DETAILS:

The script involves some initial setup (creating the source control table if it doesn't exist, as well we setting up the SQL string that is executed), but there are three major segments that do the heavy lifting.

     DECLARE object_cursor CURSOR FOR 
                            SELECT DISTINCT id
                              FROM syscomments sc (NOLOCK)
                             WHERE sc.id > 0
                               AND OBJECTPROPERTY(sc.id, 'IsMSShipped') = 0
                               AND [TEXT] IS NOT NULL

Open in new window


The initial cursor setup code selects which objects we're interested in - if there's any additional filter you want done (like excluding objects that start with "sp_", or if you only want stored procedures instead of all objects), you should do it using a WHERE clause here. In the provided segment, the only filters applied are just those that help filter our system objects - for this, "OBJECTPROPERTY(sc.id, 'IsMSShipped') = 0" is doing the heavy lifting, since it's the most reliably way to tell if an object is a system-included object, and it's supported as far back as SQL 2000.

The next segment that does major work is the actual INSERT into the source control database table - it's a multi-part query that links back to source control table a few times:

      INSERT INTO master..coSourceControl
                            OUTPUT ''1'' INTO #Results
                            SELECT s0.*
                              FROM (select db_id() as databaseid,
                                           @object_id as objectid,
                                           @object_name as objectname,
                                           @ObjectText as objecttext,
                                           @SourceTimestamp as sourcedate) s0
                              left
                              join (SELECT i1.*
                                      from master..coSourceControl i1
                                      join (SELECT databaseid, objectid, MAX(SourceDate) as SourceDate
                                              FROM master..coSourceControl
                                             GROUP BY databaseid, objectid) i2
                                        on i1.databaseid = i2.databaseid
                                       and i1.objectid   = i2.objectid
                                       and i1.sourcedate = i2.sourcedate) s1
                                on s0.databaseid = s1.databaseid
                               and s0.objectid   = s1.objectid
                             WHERE s0.objectname <> isnull(s1.objectname, '''')
                                OR s0.objecttext <> isnull(s1.objecttext, '''')

Open in new window


The OUTPUT clause is used to support the summary at the end of the whole process, and doesn't have any impact on the actual source control process itself. It only outputs a row when something is updated, so you can keep track of how many updates have been made throughout the process

It's easier to understand the query starting with the second virtual table, which is nested - it's being used to select the most recent version of the object in question (if a previous version of it exists). By selecting the MAX(SourceDate) for the object in the inner query, you can fetch the related text for that version in the outer query.

Once you have the most recent version (or a table with all NULL values if a previous version doesn't exist), then it compares it to the version you're currently working with - if the current version hasn't changed, then then both the name ObjectName and ObjectText will match, and nothing is inserted. If either of those fields is different, then it sees that it needs to save a new version of the object, and inserts it properly.

The last segment "puts it all together":

	CREATE TABLE #Results (ObjectsUpdated INT)
                      	DECLARE @ObjectsUpdated INT
                      	
                      	exec sp_msforeachdb @SQL
                      
                      	SELECT @ObjectsUpdated = ISNULL(SUM(ObjectsUpdated), 0)
                      	  FROM #Results
                      	
                      	SELECT @ObjectsUpdated as [Objects Updated]
                      	
                      	RETURN @ObjectsUpdated

Open in new window


It uses the undocumented stored procedure "sp_MSforeachdb" (read about it here) - this stored procedure will run a given command in each database on the instance, and it's what allows this process to automatically support new databases on the server. When you add a new database to the server, it automatically becomes part of the list processed by sp_MSforeachdb.

A temp table is created to hold the results of every insert - each time a new version of an object is found and added the coSourceControl table, a record is added to this temp table (using the OUTPUT clause from the nested INSERT above). At the end, a SUM of this rowcount is taken to give the total number of objects for which new versions were found and recorded.

IMPLEMENTING IT:

To implement it, just grab the script from the bottom of the article and run it in the master database - it will create the stored procedure called coSourceControlRefresh. That's it - now either run it on demand whenever you want to back up a copy of your schema history, or you can schedule it to run on any desired interval using the SQL Agent. It will create the supporting table (if it's missing) and scan every database every time it's run. To see the history for an object:

SELECT db_name(databaseid) as [Database],
                             object_name(objectid) as [Object Name],
                             SourceDate,
                             ObjectText
                        FROM master.dbo.coSourceControl
                       WHERE db_name(databaseid) LIKE '%Your database name%'
                             object_name(objectid) LIKE '%The name of some object%'
                      ORDER BY SourceDate DESC

Open in new window


Restoring a dropped or changed database object should be as simple as running the query above, grabbing the contents of ObjectText you're interested in, and then pasting it in another window and executing it. That's all it takes to restore the previous version of an object (and this stored proc should also, the next time it runs, see that you've altered the object and record that there's a "new" version of it).

Here's the complete contents of the script - grab this and run it in your master database to create the supporting stored procedure, and then you're ready to go!

ALTER PROC dbo.coSourceControlRefresh
                      AS
                      BEGIN
                      
                      	SET NOCOUNT ON
                      
                      	IF NOT EXISTS (SELECT * FROM master..sysobjects where name like 'coSourceControl')
                      	CREATE TABLE master..coSourceControl (databaseid INT, objectid INT, ObjectName [sysname] NOT NULL, ObjectText NVARCHAR(MAX), SourceDate DATETIME)
                      
                      	DECLARE @SQL NVARCHAR(MAX)
                      		SET @SQL = '
                          USE ?
                          SET NOCOUNT ON
                      
                          DECLARE @SourceTimestamp DATETIME,
                                  @Object_id INT,
                                  @Object_name SYSNAME,
                                  @ObjectText NVARCHAR(MAX)
                      
                              SET @SourceTimestamp = GETDATE()
                      
                           DECLARE object_cursor CURSOR FOR 
                            SELECT DISTINCT id
                              FROM syscomments sc (NOLOCK)
                             WHERE sc.id > 0
                               AND OBJECTPROPERTY(sc.id, ''IsMSShipped'') = 0
                               AND [TEXT] IS NOT NULL
                      
                             OPEN object_cursor;
                      
                            FETCH NEXT FROM object_cursor
                             INTO @object_id
                      
                          WHILE @@FETCH_STATUS = 0
                          BEGIN
                      
                            SET @ObjectText = ''''
                      
                            SELECT @ObjectText = @ObjectText + [text]
                              FROM syscomments
                             where id = @object_id
                             order by colid
                      
                            SELECT @Object_Name = object_name(@object_id)
                      
                            INSERT INTO master..coSourceControl
                            OUTPUT ''1'' INTO #Results
                            SELECT s0.*
                              FROM (select db_id() as databaseid,
                                           @object_id as objectid,
                                           @object_name as objectname,
                                           @ObjectText as objecttext,
                                           @SourceTimestamp as sourcedate) s0
                              left
                              join (SELECT i1.*
                                      from master..coSourceControl i1
                                      join (SELECT databaseid, objectid, MAX(SourceDate) as SourceDate
                                              FROM master..coSourceControl
                                             GROUP BY databaseid, objectid) i2
                                        on i1.databaseid = i2.databaseid
                                       and i1.objectid   = i2.objectid
                                       and i1.sourcedate = i2.sourcedate) s1
                                on s0.databaseid = s1.databaseid
                               and s0.objectid   = s1.objectid
                             WHERE s0.objectname <> isnull(s1.objectname, '''')
                                OR s0.objecttext <> isnull(s1.objecttext, '''')
                      
                             FETCH NEXT FROM object_cursor
                              INTO @object_id
                      
                          END
                          CLOSE object_cursor;
                          DEALLOCATE object_cursor;'
                      
                      	-- Actually run it for each database
                      	CREATE TABLE #Results (ObjectsUpdated INT)
                      	DECLARE @ObjectsUpdated INT
                      	
                      	exec sp_msforeachdb @SQL
                      
                      	SELECT @ObjectsUpdated = ISNULL(SUM(ObjectsUpdated), 0)
                      	  FROM #Results
                      	
                      	SELECT @ObjectsUpdated as [Objects Updated]
                      	
                      	RETURN @ObjectsUpdated
                      
                      END

Open in new window

2
3,873 Views
Ryan McCauleySenior Data Architect
CERTIFIED EXPERT
I'm a lover of data - management, querying, and presentation. I'm happy to help make things reliable, quick, and arranged to tell a story.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.