LIST ALL HEAP TABLES | SqlHints.com

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:

SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I		
	INNER JOIN sys.tables T 
		ON I.object_id = T.object_id 
WHERE I.type = 0 AND T.type = 'U'

Let us understand this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Non-HEAP Table Customers Table 
with clustered and Non-clustered index.*/
CREATE TABLE dbo.Customers (
  CustomerId int IDENTITY (1, 1) 
         PRIMARY KEY CLUSTERED NOT NULL,
  FirstName Varchar(50),
  LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_Customers
	ON dbo.Customers(FirstName, LastName)
GO
/*Let us create a HEAP Table 
(i.e. table without any Clustered Indexes)*/
CREATE TABLE dbo.Orders (
	OrderId int IDENTITY (1, 1) NOT NULL ,
	CustomerId int NOT NULL ,
	CreationDT DATETIME NOT NULL)
GO

Now let us run the query to get all the HEAP Tables or tables that doesn’t have Clustered Index and verify the result:

Share this: