I reckon you should have more than just the "current version of the whole thing"
We have a table called something like "ScriptRunLog" and that contains the name of the script, run-date/time and the version number.
Each of our "scripts" (initial build, each "patch" script for new version, etc.) inserts a row into this table. That tells use the Current version (SELECT MAX(VersionNumber) WHERE ScriptName = 'MAIN_VERSION') but also when the various scripts were run, AND what order they were run in.
Every Sproc script (i.e. a script to create an Sproc) has the SProc name and version number (version number is in the format yyyymmddxxx - where "xxx" is any subdivider - such as indicating a bespoke modification for a specific customer).
We have a routine which will delete entries in this table that are, say, > 6 months old AND there is a newer entry for that named-script - so we can purge the stale stuff after a while.
We have an Sproc that creates a row in the logging table; for example all Sproc scripts start something like:
PRINT 'Create procedure usp_MySproc'
GO
EXEC dbo.usp_MyLogScriptRun 'usp_MySproc', '070107'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[usp_MySproc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.usp_MySproc
GO
CREATE PROCEDURE dbo.usp_MySproc
...
usp_MyLogScriptRun takes parameters for the Sproc name and an optional "Version" number. Leave the version number off and it displays the log history (TOP 100 ORDER BY CreateDate) - so I can just highlight the
EXEC dbo.usp_MyLogScriptRun 'usp_MySproc'
bit and execute it to see when it was last run etc. Include the version number and it inserts a new row.
Some discipline needed to edit the VersionNumber parameter each time the Sproc script is saved, but that becomes second nature after a bit.
We have a Query that will compare two given databases - we use that to compare a newly updated database against a "known-good" master database to see if we forgot anything!
Kristen
Database version
Posted - 01/07/2007 : 07:35:38
