SQL Prompt formatting -- incorrectly qualifying constraint names

Recently I've noticed a problem when attempting to use the formatting feature of SQL Prompt.  The "qualify object names" step is adding the table name to the name of constraints in some circumstances.  This isn't valid syntax.  Here is a short code snippet that exhibits this behavior.  The DROP constraint has the table name prefixed to it when formatted; the ADD constraint does not.  It seems to happen only when the database that is open in SQL Server Management Studio already contains this table.

IF OBJECT_ID(N'dbo.mtzcus_2022RY_OIDValueSet', N'U') IS NULL
   CREATE TABLE dbo.mtzcus_2022RY_OIDValueSet
      (
         ValueSetOID          VARCHAR(64)  NOT NULL
        ,Code                 VARCHAR(32)  NOT NULL
        ,CodeSystemOID        VARCHAR(64)  NOT NULL
        ,ValueSetVersion      VARCHAR(255)
        ,ValueSetDisplayName  VARCHAR(256)
        ,CodeDisplayName      VARCHAR(1024)
        ,CodeSystemVersion    VARCHAR(32)
        ,CodeSystemName       VARCHAR(32)  NOT NULL
        ,IMOCodeSystemName    VARCHAR(32)  NOT NULL
        ,ValueSetSource       VARCHAR(256)
        ,ValueSetType         VARCHAR(32)
        ,ValueSetBinding      VARCHAR(32)
        ,ValueSetStatus       VARCHAR(32)  NOT NULL
        ,ValueSetRevisionDate DATETIME
        ,MeasureType          VARCHAR(8)   NOT NULL
        ,RowUpdateDateTime    DATETIME     NOT NULL
      );
GO


/* Primary key */
IF EXISTS
   (
      SELECT
         1
      FROM
         sys.key_constraints
      WHERE
         [type] = 'PK'
         AND name = N'mtzcus_2022RY_OIDValueSet_PK'
         AND OBJECT_NAME(parent_object_id) = N'mtzcus_2022RY_OIDValueSet'
   )
   ALTER TABLE dbo.mtzcus_2022RY_OIDValueSet
   DROP
   CONSTRAINT mtzcus_2022RY_OIDValueSet_PK;
GO

ALTER TABLE dbo.mtzcus_2022RY_OIDValueSet
ADD
   CONSTRAINT mtzcus_2022RY_OIDValueSet_PK
   PRIMARY KEY CLUSTERED ( ValueSetOID ASC, Code ASC, CodeSystemOID ASC, IMOCodeSystemName ASC );
GO
Tagged:

Best Answer

Answers

  • Eddie DEddie D Posts: 1,805 Rose Gold 5
    Hi,
    Thank you for your forum post.

    What version of SQL Prompt are you using?

    At the moment, I am unable to replicate the reported fault symptoms.  Can you please provide the DDL prior to and after, using the "qualify object names" step?

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Dan_JDan_J Posts: 464 Silver 2
    Hi @dbrinkerhoff

    Our apology for the delay in acknowledging your last message, I'm glad this issue was resolved by v.10.11.10 of SQL Prompt!

    Thanks for coming back to us to confirm this.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.