How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155

Question: How can I list the index types on every user table in a SQL Server database?

Answer: Read sys.indexes in the target database, joined to the table and schema catalogs. I answered an earlier version of this question years ago, before columnstore and some newer index types were part of the conversation. It came up again in an interview, and the inventory is also useful in my database performance health checks. The important first step is to decide whether you want every index name or a one-row-per-table summary.

List each index. This query returns one row per catalog index entry in the current database. A heap appears as type HEAP with no index name; that row describes base table storage rather than a named index.

SELECT s.name AS SchemaName,
       t.name AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
ORDER BY s.name, t.name, i.index_id;

Summarize by table. My original script returned one row per table with separate columns for base storage, nonclustered rowstore, nonclustered columnstore, and other indexes. The following version keeps that useful shape with fewer joins. It also groups any index type outside the named categories in OtherIndexes instead of assuming the older hard-coded list is exhaustive.

SELECT s.name AS SchemaName,
       t.name AS TableName,
       MAX(CASE WHEN i.type IN (0, 1, 5)
                THEN i.type_desc END) AS BaseStorage,
       SUM(CASE WHEN i.type = 2 THEN 1 ELSE 0 END)
           AS NonclusteredRowstore,
       SUM(CASE WHEN i.type = 6 THEN 1 ELSE 0 END)
           AS NonclusteredColumnstore,
       SUM(CASE WHEN i.type NOT IN (0, 1, 2, 5, 6)
                THEN 1 ELSE 0 END) AS OtherIndexes
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

Here 0 is a heap, 1 a clustered rowstore index, 5 a clustered columnstore index, 2 a nonclustered rowstore index, and 6 a nonclustered columnstore index. SQL Server has additional index types, and the catalog may evolve, so use the first query when you need their actual names and descriptions rather than relying on the summary bucket. Microsoft’s sys.indexes reference defines the current type values.

That is the practical interview distinction: first show the individual indexes; then aggregate only if the question asks for a count per table. A summary is quick to scan, but it should not hide which particular index needs attention. If you have a useful addition to this health-check query, share it in the comments and I will credit it.

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