In a previous Index Basics post we went over the importance of having a Clustered Index on your tables, and we learned that SQL Server will automatically create a Clustered Index on the Primary Key column(s) of your table, unless you explicitly tell it not to.
But what about tables that don’t have Primary Keys? Is there a way to get a list of them and tell if they do or do not have a Clustered Index? Yes there is. SQL Server makes this information easily available via the sys.indexes DMV. In the below query, I also join on the sys.objects and sys.schemas DMVs to get the table and schema names.
SELECT [TableName] = s.[name]+'.'+o.[name]
FROM sys.indexes i
INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE i.[type] = 0 -- '0' means 'Heap', which is a table with no clustered index
AND o.[type_desc] = N'USER_TABLE' --only check user created tables
ORDER BY [TableName]
This query will return a list of tables that do not have a Clustered Index. It is database specific, so it only returns results from the database that you are running it against.