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] =, table_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
LEFT JOIN (SELECT object_id,COUNT(Index_id) OtherIndex
FROM sys.indexes
WHERE type IN (3,4,7)
GROUP BY object_id) I3
LEFT JOIN (SELECT object_id,COUNT(Index_id) NC_ColumnStoreIndex
FROM sys.indexes
WHERE type = 6
GROUP BY object_id) I4
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 (

, , , ,
Previous Post
How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154
Next Post
How to Get Top N Records Per Group? – Ranking Function – Interview Question of the Week #156

Related Posts

4 Comments. Leave new

  • Julio Cesar Sanchez Lobo
    January 7, 2018 7:09 am

    Hi Pidal,

    Thank you for your feedback and information about SQL SERVER and I need by one more your help in if you can send me the process to install the cluster in SQL SERVER.


    I’ll waiting for your help me.

  • Excellent..!!

  • Script is good, as usually. However I had lots of errors due to the fact that my system is case-sensitive. For the future, could you please refine your scripts for case-sensitive installations?

    • Thanks Mark, I will follow that.

      Though the challenge is that SQL Server keeps on changing their table names from version to version and I am often lost which version of SQL Server to follow.

      Still what you have said is very valid and let me put extra efforts to make it right.


Leave a Reply Cancel reply

Exit mobile version