Wednesday 27 April 2011

HEAPS of fun : Tables WITHOUT clustered indexes

Tsql to list Tables WITHOUT clustered indexes (aka HEAPS)

SQL 2008/2005 Version
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2
or
select name from sys.tables  
where object_id not in (select object_id from sys.indexes where type_desc = 'CLUSTERED')

SQL 2000 Version
SELECT DISTINCT 
  o.name  AS TableName
FROM sysindexes i
INNER JOIN sysobjects o
ON i.id = o.id 
WHERE indid = 0
AND o.type = 'U'
ORDER BY 1
or
SELECT name
FROM sysindexes
WHERE indid = 0
AND OBJECTPROPERTY(id,'IsUserTable') = 1
ORDER BY 1
HEAP tables and the number of records they contain.
SELECT sysobjects.name, sysindexes.rows
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'u'
AND sysindexes.indid < 2
AND sysobjects.name IN 
(    SELECT DISTINCT   
      o.name  AS TableName  
    FROM sysindexes i  
    INNER JOIN sysobjects o  
    ON i.id = o.id   
    WHERE indid = 0  
    AND o.type = 'U'  

)
order by sysindexes.rows desc

No comments: