如何查询以查明表是否具有 CLUSTERED 主键 [英] How to Query to Find out if a Table has a CLUSTERED Primary Key

问题描述

我发现了这个问题，但它似乎没有回答这个问题...

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

SQL Server - 如何查找聚集索引是否存在

如何写一个 IF T-SQL 语句来表示:

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 

推荐答案

试试这个

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

聚集索引总是有 index_id = 1.当然 - 如果你像这样检查(使用 is_primary_key = 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".

更新:或者如果使用 index_id = 1 对您来说似乎是黑魔法，您也可以使用 type 列代替:

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

这篇关于如何查询以查明表是否具有 CLUSTERED 主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！