Find Tables Without Clustered Indexes


Need a way to find all tables without a clustered index in SQL Server? Running this script every once in awhile will help ensure best practices are being followed and every table has a clustered index. This script is compatible with SQL Server 2005 and SQL Server 2008.

–Tables without a clustered index
SELECT DISTINCT
OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
OBJECT_NAME(OBJECT_ID) AS  TableName
FROM
SYS.INDEXES
WHERE
INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID, ‘IsUserTable’) = 1
ORDER BY
OBJECT_SCHEMA_NAME(object_id),
OBJECT_NAME(OBJECT_ID)


Leave a Reply

Your email address will not be published.