SQL Prompt formatting -- incorrectly qualifying constraint names

dbrinkerhoff

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

dbrinkerhoff

It looks like this was fixed with the current version of SQL Prompt (10.11.10.27545).  I can't recreate the problem.

Eddie D

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

Dan_J

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.