Microsoft – SQLServer – Do you have Heaps

Code

Do you have some hips (heaps) in your database.

Here is one way to find out….


; with cteIndexAll
(
	  [object_id]
	, [count]

)
as
(

	select 
			  [object_id]
				= tblSI.object_id

			, [count]
				= count(*)

	from   sys.indexes tblSI

	/*
		Leave out Heaps						
	*/
	where tblSI.index_id != 0	

	group by

		tblSI.object_id

)
select 
			  [databaseName]
				= db_name()

			, [schemaName]
				= schema_name(tblObject.schema_id)

			, [objectName]
				= tblObject.[name]

			, [numberofRecords]
				= sum(tblStat.row_count)

			, [primaryKey]
				= tblConstraint.CONSTRAINT_NAME

			, [numberofIndexes]
				= COUNT(tblIndexAll.object_id) 

from   sys.objects tblObject

inner join sys.indexes tblIndex
				
	on  tblObject.object_id = tblIndex.object_id
	and tblIndex.index_id = 0	--Heap	

inner join sys.dm_db_partition_stats tblStat
				
        on tblIndex.object_id = tblStat.object_id
                      
		and tblIndex.index_id = tblStat.index_id								


left outer join cteIndexAll tblIndexAll
				
	on    tblObject.object_id = tblIndexAll.object_id
					

left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblConstraint
				
	on schema_name(tblObject.schema_id) = tblConstraint.TABLE_SCHEMA
	and tblObject.name = tblConstraint.TABLE_NAME
													
	and tblConstraint.TABLE_CATALOG = db_name()
	and tblConstraint.CONSTRAINT_TYPE = 'PRIMARY KEY'
					  
where tblObject.[type] in ('U')
														  
group by 		
		  schema_name(tblObject.schema_id)
		, tblObject.[name]
		, tblConstraint.CONSTRAINT_NAME				  

order by
	sum(tblStat.row_count) desc


References:

  1. Louis Davidson – sys.dm_db_partition_stats
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s