How to know if a table has a non-unique clustered index?

• keithy_sunny

  Mr or Mrs. 500

  Points: 566

  More actions

  October 30, 2015 at 9:48 pm

  #322221

  Hi,

  Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?

  Thanks

• Jacob Wilkins

  One Orange Chip

  Points: 27972

  More actions

  October 31, 2015 at 9:30 am

  #1836865

  You can just query sys.indexes for that, looking for the object_id of your table, a type=1 (clustered), and is_unique=0.

  Something like this:

  CREATE TABLE YourTable (ID int)

  CREATE CLUSTERED INDEX CI_YourTable_ID ON YourTable (ID)

  SELECT * FROM sys.indexes

  WHERE OBJECT_ID=OBJECT_ID('YourTable')

  AND type=1 AND is_unique=0

  DROP TABLE YourTable

  Cheers!

• johnwalker10

  SSCrazy Eights

  Points: 9124

  More actions

  November 2, 2015 at 10:08 pm

  #1837258

  SELECT OBJECT_NAME(object_id) AS TableName ,

  name AS IndexName ,

  type_desc AS IndexType ,

  CASE WHEN is_unique = '1' THEN 'UNIQUE'

  WHEN is_unique = '0' THEN 'NON UNIQUE'

  END AS Index_Desc

  FROM sys.indexes

  WHERE type_desc = 'CLUSTERED'

  AND OBJECT_NAME(object_id) = 'MyTable';