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)

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *