Problem with unnamed contraints

RexJolliffRexJolliff Posts: 1 New member
in SQL Change Automation
We have problems when someone has creates a constraint in certain ways which causes the generation of a random name for the constraint (ie DF_Table_xx_A89FDC). ReadyRoll generates a DROP CONSTRAINT at the beginning of the script which fails at any site other than development (or at least any site where the constraint was created the same way).  We replace the errant drop with code similar to the following to fix this:

IF EXISTS (SELECT  o.name 
  FROM  sys.objects  o
  JOIN  sys.syscolumns  c  ON  c.cdefault = o.object_id
  WHERE  o.parent_object_id = OBJECT_ID(N'Rooms')
AND  o.type = 'D'
    AND  c.name = N'MediaDeliveryMethod')
BEGIN
DECLARE  @command NVARCHAR(MAX)

SELECT  @command = 'ALTER TABLE [dbo].[Rooms] DROP CONSTRAINT [' + o.name  + ']'
  FROM  sys.objects  o
  JOIN  sys.syscolumns  c  ON  c.cdefault = o.object_id
  WHERE  o.parent_object_id = OBJECT_ID(N'Rooms')
AND  o.type = 'D'
    AND  c.name = N'MediaDeliveryMethod';

EXECUTE  (@command);
END;
GO

Would it be possible to get ReadyRoll to generate code like this at least when it detects auto-generated constraint names? Also for primary keys it would be appreciated. (our template code looks different for primary keys)

Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.