[SQL Server] - DDL For *Existing* Index Gets Table Wrong

[LeightonRJones]

Description

See screengrab:

It's the right index, but the wrong table. The table in the DDL exists; but is in an entirely different database that exists on the same server.

DBeaver Version

Version 24.0.0.202403031740

Operating System

Windows 10

Database and driver

Microsoft JDBC Driver 8.2 for SQL Server 8.2.0.0

Steps to reproduce

Not entirely sure, to be honest :/

It seems to have something to do with if you have a database + schema 'bolded' in the Database Navigator, but you either: a) expand a different database / schema / table / index tree by clicking just the 'expand' (+) icons and then immediately RH click the 'create SQL -> DDL' menu option (without specifically 'selecting' some component of the Navigator tree) or

b) you have already expanded the 'tree' to view and can see the index you want to generate the DDL for; but you have a different database/schema/table 'bolded' in the Database Navigator.

I haven't exactly narrowed down what the circumstances are. Sorry.

Additional context

No response

[Matvey16]

@LeightonRJones Can you please reupload the image in some other way. I can't open it because of of github restrictions not allowing me to open private images

[LeightonRJones]

As requested, retrying as an attachment...

[LeightonRJones]

I just noticed that the screenshot is really horrible to read. Sorry about that.

From the dBeaver side: (a) The top highlighted text should read 'reflection_maximo.backroom.wostatus'; which is the correct table. (b) The left-hand highlighted text is just legible and reads 'WOSTATUS'. (c) The greyed (selected) index is 'WOSTATUS_VALID_FROM_VALID_TO'. (d) The right-hand highlighted text (as well as the text in the 'Generate SQL popup) reads 'ON dbbr_reflection_pason.WOSTATUS', which is incorrect. The table portion of the DDL (WOSTATUS) is correct - but the 'dbbr_reflection_pason' is incorrect. dbbr_reflection_pason is a schema in an entirely different database. It should be what's shown in (a) which is 'reflection_maximo.backroom.wostatus'.

The index name is correct; and the columns are correct; so - yeah. Odd.

[LonwoLonwo]

Hello @LeightonRJones

Sorry for the late response. Could you please provide DDL examples for testing? Starting from schema. Thanks.

[MrThwibble]

Sure.

`-- reflection_maximo.backROOM.WOSTATUS definition

-- Drop table

-- DROP TABLE reflection_maximo.backROOM.WOSTATUS;

CREATE TABLE reflection_maximo.backROOM.WOSTATUS ( PK_HASH char(64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, DATA_HASH char(64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, WONUM nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, STATUS nvarchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CHANGEDATE datetime2 NOT NULL, CHANGEBY nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, MEMO nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, GLACCOUNT nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, FINCNTRLID nvarchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ORGID nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, SITEID nvarchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, WOSTATUSID bigint NOT NULL, PARENT nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ROWSTAMP bigint NOT NULL, VALID_FROM datetime2 NOT NULL, VALID_TO datetime2 NOT NULL, CONSTRAINT WOSTATUS_PK PRIMARY KEY (WOSTATUSID) ); CREATE NONCLUSTERED INDEX WOSTATUS_CHANGEDATE_IDX ON dbbr_reflection_pason.WOSTATUS ( CHANGEDATE ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_DATA_HASH_IDX ON dbbr_reflection_pason.WOSTATUS ( DATA_HASH ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_PK_HASH_IDX ON dbbr_reflection_pason.WOSTATUS ( PK_HASH ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_SITEID_IDX ON dbbr_reflection_pason.WOSTATUS ( SITEID ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_STATUS_IDX ON dbbr_reflection_pason.WOSTATUS ( STATUS ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_VALID_FROM_VALID_TO ON dbbr_reflection_pason.WOSTATUS ( VALID_FROM ASC , VALID_TO ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX WOSTATUS_WONUM_IDX ON dbbr_reflection_pason.WOSTATUS ( WONUM ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE UNIQUE NONCLUSTERED INDEX WOSTATUS_WOSTATUSID_IDX ON dbbr_reflection_pason.WOSTATUS ( WOSTATUSID ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ; CREATE NONCLUSTERED INDEX reflection_sync_merge_WOSTATUS_idx ON dbbr_reflection_pason.WOSTATUS ( ROWSTAMP ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;`

dbbr_reflection_pason is a schema in a completely different database, a database called 'reflection_pason'. It looks like dBeaver is doing a lookup on some ID that just happens to match an ID in a different database. WOSTATUS does not exist in reflection_pason, only reflection_maximo.

[LeightonRJones]

Hi (again), This might be related. dBeaver doesn't delimit columns properly when generating DDL the ddl for an index, but does generate the DDL properly if the index is part of the PK. For example:

CREATE TABLE reflection_maximo.model.workOrderFacts ( workOrderIDSK int NOT NULL, workOrderStateSK int NOT NULL, workOrderStatusGroupSK int NOT NULL, actualLaborSK int NOT NULL, actualLaborCraftSK int NOT NULL, actualMaterialSK int NOT NULL, plannedLaborSK int NOT NULL, plannedLaborCraftSK int NOT NULL, plannedMaterialSK int NOT NULL, workOrderHierarchySK int NOT NULL, woClassTypeSK int NOT NULL, locationsSK int NOT NULL, locationHierarchyFlatPK int NOT NULL, [classstructureSK<-location] int NOT NULL, [classStructureHierarchySK<-location] int NOT NULL, [classification<-locationClassification] int NOT NULL, assetSK int NOT NULL, assetHierarchyFlatPK int NOT NULL, [classStructureSK<-asset] int NOT NULL, [classStructureHierarchySK<-asset] int NOT NULL, [classification<-asset] int NOT NULL, companyPK int NOT NULL, intraStateDay int NOT NULL, [actualLabor:actualStartDateTime] datetime NULL, [actualLabor:actualFinishDateTime] datetime NULL, [actualLabor:StartFinishDurationHours] decimal(12,2) NULL, [actualLabor:regularHoursWork] decimal(12,2) NULL, [actualLabor:regularHoursTravel] decimal(12,2) NULL, [actualLabor:CostWork] decimal(12,2) NULL, [actualLabor:CostTravel] decimal(12,2) NULL, [actualLabor:totalRegularHours] decimal(12,2) NULL, [actualLabor:totalCost] decimal(12,2) NULL, [actualMaterial:quantity] decimal(12,2) NULL, [actualMaterial:unitCostCAD] decimal(12,2) NULL, [actualMaterial:totalCostCAD] decimal(12,2) NULL, [plannedLabor:laborHours] decimal(12,2) NULL, [plannedLabor:laborRate] decimal(12,2) NULL, [plannedLabor:laborCost] decimal(12,2) NULL, [plannedMaterial:itemQuantity] decimal(15,2) NULL, [plannedMaterial:unitCost] decimal(12,2) NULL, [plannedMaterial:totalItemCost] decimal(12,2) NULL, transformationLogBatchID int NOT NULL, latestworkOrderIDSK int NULL, CONSTRAINT workOrderFacts_PK PRIMARY KEY (workOrderIDSK,workOrderStateSK,workOrderStatusGroupSK,actualLaborSK,actualLaborCraftSK,actualMaterialSK,plannedLaborSK,plannedLaborCraftSK,plannedMaterialSK,workOrderHierarchySK,woClassTypeSK,locationsSK,locationHierarchyFlatPK,[classstructureSK<-location],[classStructureHierarchySK<-location],[classification<-locationClassification],assetSK,assetHierarchyFlatPK,[classStructureSK<-asset],[classStructureHierarchySK<-asset],[classification<-asset],companyPK,intraStateDay) );

Everything is fine, however if I ask for the DDL of just the backing index nothing gets delimited, and the column references get mangled. I've had to show this as a screenshot because pasting in just results in a misleading output:

[LeightonRJones]

I just noticed that the generated DDL for the PK index also starts:

CREATE UNIQUE CLUSTERED INDEX workOrderFacts_PK ON WorkSpace.workOrderFacts

Seems a bit dangerous having 'Workspace' in there, rather than the actual schema.