Thursday, May 17, 2012

Script To Find Out Heap Tables

Table without a clustered index is called Heap Table. You can find out the heap Table by querying the sys.indexes / sys.partitions against the index_id column. As recommended all tables should have clustered indexes.

Here’s a the script written using dynamic management views that can list all the Heap Tables where Tables without have any clustered Indexes in a SQL Server 2005/2008 Database. 

use AdventureWorks
SELECT SCHEMA_NAME(o.schema_id) AS [Schema]
,object_name(A1.object_id ) AS [Table Name] , B1.rows ,user_seeks ,user_scans
,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookup
FROM sys.indexes A1
INNER JOIN sys.objects o ON A1.object_id = o.object_id
INNER JOIN sys.partitions B1 ON A1.object_id = B1.object_id AND A1.index_id = B1.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON A1.object_id = ius.object_id AND A1.index_id = ius.index_id
WHERE A1.type_desc = 'HEAP'
ORDER BY rows desc

Here is the Out Put of above query:

Clustered Tables vs Heap Tables. Script To Find Out Heap Tables. Script List all heap tables. SQl Server tables to heap or not to heap. Find all HEAP tables in SQL 2000. Find all HEAP tables in SQL 2005. Find all HEAP tables in SQL 2008. Find all HEAP tables in SQL 2008 r2. Find all HEAP tables in SQL 2012. Find Tables Without Primary Keys. Removing Forwarded Records from Heap Tables. table types. Script to find out Heap Table. Table without clustered Index. SQL Server Tables without a Clustered Index. Find all tables that are missing clustered index. Determine fragmentation of HEAP table. Clustered Index Tables and Heap Tables. Heap Structures. Indexes and Index Organized Tables. Heap Table in SQL 2012.


Post a Comment