DatabaseMetaData getIndexInfo does not include COLUMNSTORE Indexes

[goranschwarz]

Driver version

12.8.1.0

SQL Server version

Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64)
Oct 18 2024 15:31:58
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22631: ) (Hypervisor)

Client Operating System

Windows (but really any)

JAVA/JVM version

java version "21.0.1" 2023-10-17 LTS

Table schema

WideWorldImporters.Sales.InvoiceLines

Problem description

DatabaseMetaData getIndexInfo does not include COLUMNSTORE Indexes

• See Screenshot from one of my tools:
• And a Screenshot from SSMS:

Expected behavior

That all indexes that re on the table are included in the resultset

Actual behavior

Output of what you actually see.

Error message/stack trace

None

Any other details that can be helpful

Nope

JDBC trace logs

None

[machavan]

The driver calls sp_statistics procedure to get the index info and this procedure's result does not include the COLUMNSTORE indexes

e.g. 
exec sp_statistics 'tcol', 'dbo', 'tpch', '%', 'N', 'E'

We will check why it does not return this info and get back on this.

Meanwhile, as a workaround, a query similar to following on sys.indexes returning this info can be used:

SELECT 
    db_name() AS CatalogName,
    sch.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    c.name AS ColumnName,
    ic.key_ordinal AS ColumnOrder
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
INNER JOIN 
    sys.schemas sch ON t.schema_id = sch.schema_id
WHERE 
	t.name = 'tcol'
	AND sch.name = 'dbo'
ORDER BY 
    t.name, i.name, ic.key_ordinal;