Script: Find Tables Without Clustered Index

This blog post first appeared on SQLMaestros

Before we start fine tuning SQL Server, it is good to make sure all basic best practices are in place. One such best practice is to have a clustered index on every table in the database (well, not always but most of the times, having clustered index per table yields better performance, especially if you are searching on clustered key column).

So how to find out whether a table has clustered index or not. It is easy to find out for single table (Yeah, SSMS shows that for you). But if you want to find all the user tables in a database that does not have clustered indexes, then this script will become handy for you.

USE SQLMaestros
GO

SELECT DB_NAME() As DatabaseName
    ,SCHEMA_NAME(ST.schema_id) + '.' + ST.[name] AS TableName
FROM sys.tables st
WHERE ST.[Type] = 'U'
    AND OBJECTPROPERTY(ST.object_id, 'TableHasClustIndex') = 0

OBJECTPROPERTY is a metadata function in SQL Server that returns info about schema-scoped objects. In our above example, if the user table has ‘TableHasClustIndex’ property as ‘0’ then that table does not have clustered index defined.

After finding tables without clustered indexes, now you may want to consider creating one. Before that, lets also find out how many rows that each table contains. If a table has less than 1000 rows, having an index on top it, does not have considerable impact on performance, so you may choose to ignore those tables.

;WITH TableNotHasClustIndex
AS
(
SELECT DB_NAME() As DatabaseName
    ,SCHEMA_NAME(ST.schema_id) + '.' + ST.[name] AS TableName
    ,(
        SELECT SUM(SP.[rows])
        FROM sys.partitions SP
        WHERE ST.object_id = SP.object_id
        ) NoOfRows
FROM sys.tables st
WHERE OBJECTPROPERTY(ST.object_id, 'TableHasClustIndex') = 0
)
SELECT * FROM TableNotHasClustIndex
    WHERE NoOfRows > 1000

See you soon with another script.

This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.