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 obj.name as TableName
FROM sys.objects obj
INNER JOIN sys.indexes idx ON idx.OBJECT_ID = obj.OBJECT_ID
WHERE
 (obj.type='U'
 AND obj.OBJECT_ID NOT IN (
  SELECT OBJECT_ID
  FROM sys.indexes
  WHERE index_id > 1)
 AND idx.Index_ID = 1)

OUTPUT

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