How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153

Question: How do I list the clustered and nonclustered columnstore indexes in a SQL Server database, together with their table names?

Answer: Columnstore indexes have become common enough that I now hear this during my database performance health checks. The question sounds like an index problem, but the useful answer is a short catalog query that keeps the schema, table, name, and type together.

Run this in the database you want to inspect. In sys.indexes, type 5 means clustered columnstore and type 6 means nonclustered columnstore. Joining to sys.tables makes the table and schema explicit.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
       t.name AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType
FROM sys.indexes AS i
JOIN sys.tables AS t
  ON t.object_id = i.object_id
WHERE i.type IN (5, 6)
  AND i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName;

This historical WideWorldImporters result shows two nonclustered columnstore indexes and one clustered columnstore index, with the schema, table, index name and type in four columns.

This is a current-database inventory. If you remove the type predicate, the catalog will include the other index types too, but that is a different report. Microsoft’s sys.indexes documentation is the reference for the type values. Catalog visibility can also depend on your permissions, so an empty result is not proof that no index exists anywhere on the instance.

For more background, see my earlier articles on columnstore fundamentals, common columnstore questions, and the follow-up FAQ. I also wrote about a computed-column restriction I met at a customer site; check current feature support before carrying an old restriction into a new design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.