SQL Server 2008检查索引是否存在?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (238)

我想知道是否有一种方法来验证索引是否存在于特定的表的SQL Server数据库基于列的名称:

假设我运行以下脚本:

  CREATE NONCLUSTERED INDEX [MyIndexName]
  ON [dbo].[MyTable] ([CustomerId])
  INCLUDE ([Id],[ModificationDate],[ProductId])
  GO

现在我想检查索引是否存在基于表名和列(和包含子句中的列),而不是实际的索引名称。

(SQL Server 2008 R2)

提问于
用户回答回答于

大致如下:

select ...
from sys.indexes i
join sys.index_columns ic on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on ic.object_id = c.object_id
    and ic.index_column_id = c.column_id
where i.object_id = object_id('MyTable')
and (c.name = 'CustomerId' and ic.key_ordinal = 1 and ic.is_descending_key = 0)
or (ic.is_included_column = 1 and c.name in ('Id', 'ModificationDate', 'ProductId'));
用户回答回答于

尝试以下查询:

if exists(
           SELECT 1 
           FROM sys.indexes 
           WHERE name = 'INDEX' 
           AND object_id = OBJECT_ID('TABLENAME')
          )
 begin
 ....
 end

扫码关注云+社区

领取腾讯云代金券