Getting Index count for all tables

WITH cte AS
(
    SELECT   t.name AS Table_Name
	       , t.object_id
		   , i.index_id
		   , i.type
		   , i.type_desc
      FROM sys.indexes i
INNER JOIN sys.tables t
        ON i.object_id = t.object_id
     WHERE t.is_ms_shipped = 0
	   AND i.is_disabled = 0
	   AND i.is_hypothetical = 0
       AND i.type <= 2
), cte2 AS
(
SELECT *
  FROM cte c
 PIVOT (count(type) for type_desc in ([NONCLUSTERED])) pv
)
    SELECT   c2.table_name       AS TableName
           , MAX(p.rows)         AS Rows
		   , CASE WHEN (OBJECTPROPERTY(c2.object_id,'TableHasClustIndex') = 1) THEN
		        'HEAP'
		     ELSE
			    'CLUSTERED'
			 END                 AS TableType
           , sum([NONCLUSTERED]) AS NumOfNonClusteredIndexes
      FROM cte2 c2
INNER JOIN sys.partitions p
        ON c2.[object_id] = p.[object_id]
	   AND c2.index_id = p.index_id
  GROUP BY table_name, c2.object_id

Davide Mauri wrote a nice script, I have done similar work without using CTE.  So I really liked the script; I notice in his comments Rob suggested using OBJECTPROPERTY.  I haven’t used the function much, but just to get use to it I have re-write Davide’s script using Object Property and reformatted the code to my style ;-).   I like Davide’s script, because when it comes to T-SQL coding, I am fairly week.  So it was nice seeing an example double CTE + Pivot table :D.

Reference

1. SQLBlog.Com. Davide Marui. Find all the tables with no indexes at all. Link.