List all User Tables in a SQL Server Database with no Clustered Index

To list all user tables in a SQL Server database that have no Clustered Index defined, you can use the following query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.index_id = 0
AND o.type = 'U'
ORDER BY o.name, i.type

The query checks for all user tables that have a row in sys.indexes with index_id value of 0. If a table has no indexes or only non-clustered indexes, it will have have a row with index_id = 0.
A table without a clustered index is called a heap.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">