Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Find if a table has Clustered Index

This is a simple requirement where we would like to find out if a particular table has a clustered index or not. It is adviced that all tables in the database have a clustered index. As the clustered index is the place where the data is present it is important these be in place. Moreover the data is ordered using the clustered index key else the data would form a heap. And this is performance degrading factor for large databases.

Select   i.TABLE_NAME,
            Case objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')
            When 0 then 'No'
            When 1 then 'Yes'
            End as [Has Clustered Index]
From    INFORMATION_SCHEMA.TABLES i
Where  objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1
Order by [Has Clustered Index] , i.TABLE_NAME asc