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

Question: How to List All the clustered and nonclustered Indexes of Your Table in SQL Server?

I have previously blogged about this few years ago but at that time SQL Server did not supported Columnstore Indexes as well other kind of indexes. I recently received this question again in one of the interview and I decided to blog about it again. The script which I am including here is the same script I use in my Comprehensive Database Performance Health Check. There are many other script which I use during my consultancy which you can use to tune your SQL Server.

Here is the complete script:

SELECT [schema_name] = s.name, table_name = o.name,
MAX(i1.type_desc) ClusteredIndexorHeap,
MAX(COALESCE(I2.NonClusteredIndex,0)) NonClusteredIndex,
MAX(COALESCE(I4.NC_ColumnStoreIndex,0)) NC_ColumnStoreIndex,
MAX(COALESCE(I3.OtherIndex,0)) OtherIndex
FROM sys.objects o
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.indexes i1 ON o.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1,5)
LEFT JOIN (SELECT object_id,COUNT(Index_id) NonClusteredIndex
FROM sys.indexes
WHERE type = 2
GROUP BY object_id) I2
ON o.OBJECT_ID = i2.OBJECT_ID
LEFT JOIN (SELECT object_id,COUNT(Index_id) OtherIndex
FROM sys.indexes
WHERE type IN (3,4,7)
GROUP BY object_id) I3
ON o.OBJECT_ID = i3.OBJECT_ID
LEFT JOIN (SELECT object_id,COUNT(Index_id) NC_ColumnStoreIndex
FROM sys.indexes
WHERE type = 6
GROUP BY object_id) I4
ON o.OBJECT_ID = i4.OBJECT_ID
WHERE o.TYPE IN ('U')
GROUP BY s.name, o.name
ORDER BY schema_name, table_name

If table has Heap, Clustered Index or ColumnStore Clustered Index, it will be displayed in the column NonClusteredIndex. As table can have only one of the heap, clustered index or columnstore clustered index, it will be described in the words. The rest of the column will display regular (rowstore) nonclustered index and columnstore nonclustered index. Additionally, there are three different kind of indexes – XML, Spatial or Nonclustered Hash Indexes (which are only supported on In-memory OLTP) will be collected under OtherIndexes.

I believe this script works well but if you have any other such script, requesting you to provide you the same and I will publish it with due credit on this blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)