9

I found this question, but it doesn't appear to answer the question...

SQL Server - How to find if clustered index exists

How can I write an IF T-SQL statement to say:

IF NOT ([TableName] has a CLUSTERED PK)
   ALTER TABLE to add the CLUSTERED PK
Community
  • 1
  • 1
Will Strohl
  • 1,646
  • 2
  • 15
  • 32
  • 2
    How about this one: http://stackoverflow.com/questions/15091826/find-tables-without-clustered-index-but-with-primary-keys-on-a-table – peter.petrov Jan 16 '14 at 16:44

2 Answers2

17

Try this

IF NOT EXISTS (SELECT * 
               FROM sys.indexes 
               WHERE object_id = OBJECT_ID('dbo.MdsInventar') 
                 AND index_id = 1
                 AND is_primary_key = 1)
   ......

The clustered index always has index_id = 1. Of course - if you check like this (with the is_primary_key = 1 condition), then there's always a chance that there might be a non-primary clustered index on the table already - so you won't be able to create another clustered index. So maybe you need to lose the AND is_primary_key = 1 condition and check just for "is there a clustered index".

Update: or if using index_id = 1 seems black magic to you, you can also use the type column instead:

IF NOT EXISTS (SELECT * 
               FROM sys.indexes 
               WHERE object_id = OBJECT_ID('dbo.MdsInventar') 
                 AND type = 1
                 AND is_primary_key = 1)
   ......
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I think it would be more self-describing to filter on the index type instead of the (kind of) magic index_id. – usr Jan 16 '14 at 16:58
  • 1
    @usr: yes, you could do that, too - but `index_id = 1` is hardly "magic" - [it's well known, well documented](http://technet.microsoft.com/en-us/library/ms173760.aspx) ... Also: knowing that `type = 1` is the clustered index also is kinda "magic", no? – marc_s Jan 16 '14 at 16:59
  • You could also check whether it is a HEAP (nonclustered) or not (clustered): WHERE object_id = OBJECT_ID('dbo.MdsInventar') AND type_desc = 'HEAP' but then you need to change the IF NOT EXISTS to IF EXISTS – cezary Jun 06 '23 at 12:22
2

I am not sure if it is still the issue but maybe it will help another person.

Please find below portion of the code:

SELECT COL_NAME(ic.object_id,ic.column_id) AS 'column name', 
i.object_id, i.name, i.type_desc, ds.name, ds.type_desc FROM sys.indexes i
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id 
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('NameOfYourTableWithSchemaIncluded')

Hope it helps anyone! Best Regards.

kadg
  • 21
  • 1