Introduction
When developing and supporting enterprise wide applications, it's very common to see different stages of the application being run as different versions. Or even different clients may be running different versions of the application in the production environment. This article provides a template for enforcing database versioning in such a scenario.
Implementing DB Versioning
In a multiple user environment it's very common to see that some objects are created or deleted in the database for some testing and data fixing issues (like views, constraints, triggers or indexes). By using the script below, all the objects in the database are dropped except for the table and then created again. So the developers have to add the necessary objects in the script at one place and thus it ensures that when the script is run, the objects in the database confirm to what they are supposed to be.
This script doesn't drop the table as the data in the table will be critical to the application. Instead the script checks all the tables as expected by the application are existing and also the columns defined in them are in proper order. Also we can add a table in the database called DBVersion with appropriate fields and check the version number before running the script. For example: if the application is upgraded from release 1 to 2, before running it, check if the current value in the version field is 1, and after running the script, update that to 2. (This can be extended even to minor releases like 1.1.01 etc..) This will ensure that the script runs only on the database with the appropriate version.
Template Code for enforcing DB Versioning
BEGIN Transaction
PRINT 'Drop Constraints...'
GO
DECLARE @SQL nvarchar(4000)
DECLARE DropCursor CURSOR FOR
SELECT 'ALETR TABLE [' + so1.name + ']
DROP CONSTRAINT [' + so2.name + ']'
FROM sysconstraints sc
JOIN sysobjects so1 ON sc.ID = so1.ID
JOIN sysobjects so2 ON sc.constid = so2.ID
WHERE so2.xtype IN ('PK', 'F', 'D', 'UQ', 'C')
AND ObjectProperty(so2.ID, 'IsMSShipped') = 0
ORDER BY so2.xtype
OPEN DropCursor
FETCH NEXT FROM DropCursor INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
Execute sp_ExecuteSQL @SQL
FETCH NEXT FROM DropCursor INTO @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
PRINT 'Drop Indexes & Statistics...'
GO
DECLARE @SQL nvarchar(4000)
DECLARE DropCursor Cursor For
SELECT CASE
WHEN IndexProperty(so.id, si.name,
'IsStatistics') = 1 THEN
'Drop Statistics ['
ELSE 'Drop Index ['
END + so.name + '].[' + si.name + ']'
FROM sysindexes si
JOIN sysobjects so On si.id = so.id
WHERE si.indid Not In (0,255)
AND so.xtype = 'u'
AND ObjectProperty(so.id, 'IsMSShipped') = 0
OPEN DropCursor
FETCH NEXT FROM DropCursor Into @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_ExecuteSQL @SQL
FETCH NEXT FROM DropCursor Into @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
Print 'Drop Triggers...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
Select 'Drop Trigger [' + so1.name + ']'
From sysobjects so1
Join sysobjects so2 On so1.parent_obj = so2.id
Where so1.xtype = 'TR'
And ObjectProperty(so2.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
Execute sp_ExecuteSQL @SQL
Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Calculated Fields...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
Select 'Alter Table [' + so.name + ']
Drop Column [' + sc.name + ']'
From syscolumns sc
Join sysobjects so On sc.id = so.id
Where sc.IsComputed = 1
And so.xtype = 'U'
And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
Execute sp_ExecuteSQL @SQL
Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Procedures...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
Select 'Drop Procedure [' + so.name + ']'
From sysobjects so
Where so.xtype = 'P'
And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
Execute sp_ExecuteSQL @SQL
Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Views...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
Select 'Drop View [' + so.name + ']'
From sysobjects so
Where so.xtype = 'V'
And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
Execute sp_ExecuteSQL @SQL
Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Functions...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
Select 'Drop Function [' + so.name + ']'
From sysobjects so
Where xtype in ('FN', 'IF', 'TF')
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
Execute sp_ExecuteSQL @SQL
Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Create Independent Functions...'
GO
Print 'Create Base Tables...'
GO
If Not dbo.TableExists('Table1')
Create Table Table1 (
[ID] int NOT NULL ,
) ON [PRIMARY]
GO
If Not dbo.ColumnExists('Table1', 'Column1')
Alter Table dbo.Table1 Add
Column1 int NOT NULL
GO
Print 'Create Base Table Dependent Procedures...'
GO
Print 'Create Application Tables...'
GO
If (Select SysColumns.Type from Sysobjects
Join Syscolumns
On Sysobjects.ID = Syscolumns.ID
Where Sysobjects.Name = 'Table1' And Syscolumns.Name = 'Column1') = '111'
Begin
Alter Table Table1 Drop Column Column1
Alter Table Table1 Add Column1 varchar(50) Null
End
GO
Print 'Create Functions That Depend On Tables...'
GO
Print 'Create Calculated Fields...'
GO
Alter Table Table1 Add
Column1 AS dbo.Function1(param)
Print 'Create Constraints...'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
IX_Table1 UNIQUE NONCLUSTERED
(
Column1,
Column2
) ON [PRIMARY]
GO
Alter Table dbo.Table1 ADD
CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
Print 'Create Foreign Keys...'
GO
Alter Table Table1 ADD
CONSTRAINT FK_Table1_Table2 FOREIGN KEY
(
[ID]
) REFERENCES Table2 (
[FKID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
Print 'Create Views...'
GO
Print 'Create Functions That Depend On Views...'
GO
Print 'Create Indexes...'
GO
CREATE INDEX [IX_Table1_Column1Column2]
ON [dbo].[Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Column1Column2]
ON [Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Column1] ON
dbo.VENDOR_MASTER(Column1) ON [PRIMARY]
GO
Print 'Create Procedures...'
GO
Print 'Create Triggers...'
GO
Print 'Set Database version to current number'
GO
Print 'Data Updates...'
GO
GO
Commit
GO
The above script defines the sequence in which the objects in the database can be dropped and recreated. This template script needs to be maintained for the different releases of the application (with the appropriate changes). Even if there is not much change from one version to another it will be a good practice to maintain separate versioning for the template script as that will provide a better way of enforcing proper versioning of the database.