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 

Autecology

How about this one: #15092326 – Upstroke

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

Idempotent

I think it would be more self-describing to filter on the index type instead of the (kind of) magic index_id. – Germanous

@usr: yes, you could do that, too - but index_id = 1 is hardly "magic" - it's well known, well documented ... Also: knowing that type = 1 is the clustered index also is kinda "magic", no? – Idempotent

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 – Sheenasheeny

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

Colubrid