Lightweight SQL source control using a single stored procedure: sql server, sql server 2005, sql server 2008, source control, database schemas

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

1:
2:
3:
4:
5:
6:

Select all 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, '''')

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:

Select all Open in new window

Lightweight SQL source control using a single stored procedure

sql server

sql server 2005

sql server 2008

source control

database schemas

SQL Server 2005

About The Author

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server 2005 Experts