Identify Tables that have Clustered index but no NonClustered indexes

I was recently helping a client determine tables in his SQL Server 2005/2008 which had Clustered Index but did not have any nonclustered index on them. I am sharing the query I tried out to achieve the same and will use the Northwind database as a sample to test the query on

SELECT as TableName
FROM sys.objects obj
INNER JOIN sys.indexes idx ON idx.OBJECT_ID = obj.OBJECT_ID
FROM sys.indexes
WHERE index_id > 1)
AND idx.Index_ID = 1)


Tables without nonclustered index

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook


Bob S. said...

I think you need to add the following filter to ensure that the results only include tables with clustered indexes.

AND idx.INDEX_ID = 1

Otherwise, the query includes tables that don't have a clustered index (INDEX_ID = 0 uses a heap).

Suprotim Agarwal said...

Thanks Bob. Yes I missed out on that one and thanks so much for your suggestion. I have added that to the query

Srini said...

select name from sys.tables WHERE OBJECTPROPERTYEX(object_id,'TableHasclustIndex')=1 AND OBJECTPROPERTYEX(object_id,'TableHasNonclustIndex')=0