April 6, 2005 at 12:31 am
Hi Gurus,
I want to migrate DB schema changes to various sites running SQL 2000. My script drops some existing constraints and creates new ones.
The problem is when the constraints were created in past no name was provided to them and now the constraints on the same table across different databases/servers have different names (given by sql server). Now I can't have one generic db update script for all sites.
Please help with some workarounds... I can provide more details and examples if required..
Thanks,
Harman
April 6, 2005 at 6:29 am
See post in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=172275&app=true and please do not cross post.
April 8, 2005 at 10:35 am
This is the code we had used in the past. You can tweak this code to first check if the constaint exists, if it does then you may use ynamic SQL to do the required.
Hope this helps.
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
Print 'Creating Check Constraints On Table [MsgSend]...'
GO
------------------------------------------------------------------
-- Check Constraint : CHK_MsgSend_EMailSentStatus
-- ALTER TABLE MsgSend DROP CONSTRAINT CHK_MsgSend_EMailSentStatus
------------------------------------------------------------------
IF NOT Exists ( Select * From SysConstraints con
,SysObjects so1
,SysObjects so2
Where con.id = Object_id('MsgSend')
And con.id = so1.id
And con.constid = Object_id('CHK_MsgSend_EMailSentStatus')
And con.constid = so2.id
And so1.type = 'U'
And so2.type = 'C'
)
Begin
ALTER TABLE MsgSend
ADD CONSTRAINT CHK_MsgSend_EMailSentStatus
CHECK ( EMailSentStatus IN ( 0,1 ))
IF @@Error <> 0
Begin
RAISERROR('Create Check Constraint On Table [MsgSend] Failed...' , 18, 127) WITH NOWAIT
End
End
Else
Begin
RAISERROR('Check Constraint On Table [MsgSend] Already Exists...' , 0, 1) WITH NOWAIT
End
GO
paul
April 10, 2005 at 5:41 pm
Thanks Paul,
It was a lead to start with...
Cheers,
harman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply