sql Heaps

Use MyDatabase

Declare @DatabaseName varchar(20)
Set @DatabaseName = 'MyDatabase'

SELECT
	T.Name as Heap_Table,
	'Select * Into MyDatabaseArchive.dbo.' + T.Name + ' From ' + @DatabaseName + '.dbo.' + T.Name As [CopySQL],
	'Drop Table ' + @DatabaseName + '.dbo.' + T.Name As [DropSQL],
	*
FROM sys.indexes I      
INNER JOIN sys.tables T ON I.object_id = T.object_id 
WHERE
	I.type = 0
	AND T.type = 'U'
Order By T.Name Asc
Heaps are tables without a clustered index. These are generally to be avoided as reindexing is unable to reclaim lost space within them, thus the data remains fragmented and physical space is wasted. The common recommendation to fix this is to add a clustered index, reindex the table then remove the clustered index. I would suggest the clustered index is left in place unless you have a good reason not to use one. Another option is to move them to another database, or delete them altogether - if they are simply backups/dumps of other active tables.

Updated: Wednesday 25th October 2017, 18:22am

There are 0 comments

Leave a comment of your own

Comments are currently closed.