Skip to content

Instantly share code, notes, and snippets.

@wbhinton
Created December 3, 2019 16:29
Show Gist options
  • Save wbhinton/d4c12535dbb43863b45901a308f74868 to your computer and use it in GitHub Desktop.
Save wbhinton/d4c12535dbb43863b45901a308f74868 to your computer and use it in GitHub Desktop.
Three options to find heaps in MSSQL
-- Heap tables (Method 1)
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS HeapTable ,
t.Create_Date
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
AND i.type_desc = 'HEAP'
ORDER BY t.Name
--OR
-- Heap tables (Method 2)
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS HeapTable ,
t.Create_Date
FROM sys.tables t
WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0
ORDER BY t.Name;
--OR
-- Heap tables (Method 3) also provides row counts
SELECT @@ServerName AS Server ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
OBJECT_NAME(ddps.object_id) AS TableName ,
i.Type_Desc ,
SUM(ddps.row_count) AS Rows
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes i ON i.object_id = ddps.object_id
AND i.index_id = ddps.index_id
WHERE i.type_desc = 'HEAP'
AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
i.type_desc
ORDER BY TableName;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment