Find All Tables Missing Clustered Index in Database

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 

0 – HEAP
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'

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

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) 


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 []

You may also like...

1 Response

  1. Manas says:

    Dear Sir,

    This is a very nice and usefull concept.. Please keep on post further on this.


Leave a Reply

Your email address will not be published.