Find All Tables Missing Clustered Index in Database

by Manzoor Siddiqui · Published October 8, 2015 · Updated November 12, 2015

If your database contains hundreds or thousands of tables then it’s difficult to find which tables are missing clustered index in a database manually. You can go through this article which provides very simple query and analyses all the tables and provides you required result and you can check and tune your database accordingly.

You can find index type and description by using below query which will provide you output as:

SELECT DISTINCT type, type_desc FROM sys.indexes ORDER BY type 
GO

0 – HEAP
1 – CLUSTERED
2 – NONCLUSTERED
3 – XML

We will check total count of all user tables in a database with below query.

SELECT COUNT(*) FROM sys.objects WHERE type = 'U'
GO

To check total table count having CLUSTERED indexes, we can use below query.

SELECT COUNT(*) AS TotalObjecstWithClusteredIndex FROM sys.indexes 
WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = 'U') AND index_id = 1
GO

Now, to find all the objects missing CLUSTERED index, we will use query as given below.

SELECT DISTINCT name FROM sys.objects WHERE type = 'U'
AND object_id NOT IN (SELECT object_id FROM sys.indexes WHERE index_id = 1) 
ORDER BY name

Once you get the output, now you can check the tables and columns to apply clustered indexes and tune it accordingly. Similarly, you can identify tables having HEAP indexes and NONCLUSTERED indexes as well.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]