In the post - SQL Server - List all indexes of all tables  , the query returns all tables that are not heaps. In this post , we’ll look at tables without a clustered index aka Heaps

Microsoft  presents good guidelines for When to Use a Heap , When not to use a heap and Managing heaps. The purpose of this post is not replicate the Microsoft website . But to highlight the need for a DBA to review Heaps and manage them to optimize the systems

All because a table is a heap – does not automatically mean a Clustered Index is required . there are some situations where a heap is OK. Example 1: The table is very small Example 2: The RID is smaller than the clustered index key and normal query processing doesn’t use the Clustered Index and relies on the Non Clustered Index.

 One approach I use is to identify tables which are heaps i.e tables without a clustered index and are queried actively.  You can build in this process to a regular review or as part of a process when you start managing a new server.

There are plenty of situations where a DDL creating table is released onto a database but without an index

use db 
go

SELECT DISTINCT object_name(obj.object_id),idx.type_desc,ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates	  
		  FROM sys.indexes idx INNER JOIN sys.objects obj ON idx.object_id = obj.object_id 
		  INNER JOIN sys.partitions par ON idx.object_id = par.object_id AND idx.index_id = par.index_id 
		 		  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id 
		 		  WHERE idx.type_desc = 'HEAP' AND COALESCE(ius.user_scans, ius.user_lookups, ius.user_updates, ius.user_seeks) IS NOT NULL 
		          AND is_ms_shipped = 0