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)