Even thought the idea is ok but its not feasible in the production environment. we have hundreds of tables and views and stored procedures and we cannot include everything in std proc and think of maintaining that proc.
We do it differently.
We have a str proc called sp_DatabaseVersion and it returns the version of the database.
CREATE PROCEDURE sp_DatabaseVersion
@DBVersion int output,@DBType int output
SET NOCOUNT ON;
SELECT @DBVersion = 1 -- Fresh start
SELECT @DBType = 0
release 1 DBVersion = 1 and if you are deploying multiple database for different things use the dbtype to identify the database type.
and for defaults we have sp_defaults that does all the default values to all the tables.
and to update the defaults we use sp_UpdateDefaults which will update existing database defaults.
this way we can track and we have atleast 5 different versions of the database and this str proc tells us which version they are running and we use a BuildInfo table when we update the database structure to update that table with values
CREATE TABLE [dbo].[BuildInfo]
[ID] [int] NOT NULL IDENTITY(1, 1),
[BuildNumber] [int] NOT NULL,
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF_BuildInfo_LastChanged] DEFAULT (getutcdate())
) ON [PRIMARY]
which keeps track of when the build was updated.