SQL Prompt formatting -- incorrectly qualifying constraint names
dbrinkerhoff
Posts: 7 Bronze 1
in SQL Prompt
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
-
dbrinkerhoff
Posts: 7 Bronze 1
It looks like this was fixed with the current version of SQL Prompt (10.11.10.27545). I can't recreate the problem.
Answers
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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.
Dan Jary | Redgate Software
Have you visited our Help Center?