July 16, 2008 at 3:29 am
We are in software development business. We continuously add features and send database modification release script to the client. See example below for database change release script 1.1 for March 2008 and 1.2 for June 2008.
e.g.
Original Table creation script: January 2008
Create table usertable
(Column1 int)
GO
Database modification script on release 1.1 March 2008
Alter table usertable
Add Column2 datetime
Database modification script on release 1.2 June 2008
Alter table usertable
Add Column3 char(10)
Now our customer are not IT savvy. Sometimes they may not apply all the release in order. I mean they might skip database change script for release 1.1 and directly appy release 1.2.1. Now Column2 is not created and thus application error.
Can we developed script for release 1.2 in such a way that it will also include release 1.1 database change with IF Exists clause.
I mean that release 1.2 should check for existence of Column2 in the table object usertable and add column Column2 only if not exists before executing
Alter table usertable
Add Column3 char(10)
I would like to know how to achieve above objectives?
This can be done by storing release number somewhere in the control table BUT i would like to do it purely by developing SQL script.
I can also check for each columns existence before creating but it's tedious.
Microsoft gives us option to apply only last service pack during installation. It will work irrespective of previous service pack is applied or not.
July 16, 2008 at 4:08 am
You can use the sys.tables and sys.columns views but you are better off with storing a version control figure when the client misses 2 or more updates this will get nasty.
July 16, 2008 at 5:51 am
It is fairly easy to check of existance. The following would be a cumulative script:
[font="Courier New"]-- January 2008 --
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usertable]') AND type in (N'U'))
BEGIN
Create table usertable
(Column1 int)
END
GO
-- March 2008 --
IF NOT EXISTS (SELECT * FROM Information_Schema.Columns WHERE Table_Schema = 'dbo' AND Table_Name = 'usertable' AND Column_Name = 'Column2')
BEGIN
Alter table usertable
Add Column2 datetime
END
GO
-- June 2008 --
IF NOT EXISTS (SELECT * FROM Information_Schema.Columns WHERE Table_Schema = 'dbo' AND Table_Name = 'usertable' AND Column_Name = 'Column3')
BEGIN
Alter table usertable
Add Column3 char(10)
END[/font]
If you build it as a cumulative script from the beginning, all you will have to do is continue to append to the script.
This can get tricky as things get more complicated, so be careful. I also used "GO" separators in the script - which are not supported by all clients, but you get the picture.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply