写了下面的函数，允许我快速检查，看看是否存在索引;就像OBJECT_ID一样。

CREATE FUNCTION INDEX_OBJECT_ID (
    @tableName VARCHAR(128),
    @indexName VARCHAR(128)
    )
RETURNS INT
AS
BEGIN
    DECLARE @objectId INT

    SELECT @objectId = i.object_id
    FROM sys.indexes i
    WHERE i.object_id = OBJECT_ID(@tableName)
    AND i.name = @indexName

    RETURN @objectId
END
GO

EDIT:它只返回表的OBJECT_ID，但如果索引不存在，它将为NULL。我认为您可以将此设置为返回index_id，但这并不是非常有用。

你可以像这样直接选择:

SELECT * 
FROM sys.indexes 
WHERE name='YourIndexName' AND object_id = OBJECT_ID('Schema.YourTableName')

对于SQL 2008及更新版本，一个更简洁的方法，在编码方面，检测索引是否存在是使用INDEXPROPERTY内置函数:

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )  

最简单的用法是IndexID属性:

If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null

如果索引存在，则返回索引ID;如果没有，它将返回NULL。

查询指定表上是否存在聚集索引。

SELECT * FROM SYS.indexes 
WHERE index_id = 1 AND name IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Table_Name')

-- Delete index if exists
IF EXISTS(SELECT TOP 1 1 FROM sys.indexes indexes INNER JOIN sys.objects 
objects ON indexes.object_id = objects.object_id WHERE indexes.name 
='Your_Index_Name' AND objects.name = 'Your_Table_Name')
BEGIN
    PRINT 'DROP INDEX [Your_Index_Name] ON [dbo].[Your_Table_Name]'
    DROP INDEX [our_Index_Name] ON [dbo].[Your_Table_Name]
END
GO

EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
GO