You can quickly find all heap tables in SQL Server

So, what is mean?

Long story short,
a table without a clustered index is called Heap. But you can also add non clustered index without a primary key.

You can list all heap tables with the following script.

SELECT SCH.name + ‘.’ + TBL.name AS TableName FROM sys.tables AS TBL INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id AND IDX.type = 0 ORDER BY TableName

and

SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.type_desc = ‘USER_TABLE’
AND i.type_desc = ‘HEAP’
ORDER BY o.name
GO

--

--

--

I am SQL Server Database Administrator. So, I am doing this job and I am enjoying it super.

Love podcasts or audiobooks? Learn on the go with our new app.

3 Acting Exercises That Can Help Introverts Have Better Conversations

Xerox Understood the Game Has Changed and Here Is Why.

API Gateway & Caching

Announcing OpenTron

Native Android/iOS vs Flutter vs React Native

Become our community representative in your country.

Basics of FFmpeg

De-interlaced image, Armada of the Century, Rouen (FR), 1999

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alim Dogan

Alim Dogan

I am SQL Server Database Administrator. So, I am doing this job and I am enjoying it super.

More from Medium

DDL and DML in the database

Dynamic SQL and Embedded SQL