Retrieve List out Clustered Index or Non Clustered Index on Tables for Entire Database

SELECT COUNT(i.TYPE) NoOfIndex,

[schema_name] = s.name, table_name = o.name

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

WHERE o.TYPE IN ('U')

--AND i.TYPE = 1

AND i.TYPE = 2

GROUP BY s.name, o.name

ORDER BY schema_name, table_name

--select * from sys.indexes

select * from sys.objects

Kindly Bookmark and Share it: