How to Query to Find out if a Table has a CLUSTERED Primary Key

Question

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 

Answer 1

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