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.
- SQLBlog.Com. Davide Marui. Find all the tables with no indexes at all. Link.