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

Tables without nonclustered index

at

3 comments:

  1. Bob S.February 25, 2010 at 10:32 AM

    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).

    ReplyDelete
  2. Suprotim AgarwalFebruary 26, 2010 at 4:49 AM

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

    ReplyDelete
  3. SriniJanuary 10, 2011 at 10:09 AM

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

    ReplyDelete