How do you check if a certain index exists in a table?
Sql ServerSql Server Problem Overview
Something like this:
SELECT
*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME ='FK_TreeNodesBinaryAssets_BinaryAssets'
and TABLE_NAME = 'TreeNodesBinaryAssets'
but for indexes.
Sql Server Solutions
Solution 1 - Sql Server
You can do it using a straight forward select like this:
SELECT *
FROM sys.indexes
WHERE name='YourIndexName' AND object_id = OBJECT_ID('Schema.YourTableName')
Solution 2 - Sql Server
For SQL 2008 and newer, a more concise method, coding-wise, to detect index existence is by using the INDEXPROPERTY
built-in function:
INDEXPROPERTY ( object_ID , index_or_statistics_name , property )
The simplest usage is with the IndexID
property:
If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null
If the index exists, the above will return its ID; if it doesn't, it will return NULL
.
Solution 3 - Sql Server
AdaTheDEV, I used your syntax and created the following and why.
Problem: Process runs once a quarter taking an hour due to missing index.
Correction: Alter query process or Procedure to check for index and create it if missing... Same code is placed at the end of the query and procedure to remove index since it is not needed but quarterly. Showing Only drop syntax here
-- drop the index
begin
IF EXISTS (SELECT * FROM sys.indexes WHERE name='Index_Name'
AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
begin
DROP INDEX [Index_Name] ON [SchmaName].[TableName];
end
end
Solution 4 - Sql Server
If the hidden purpose of your question is to DROP
the index before making INSERT
to a large table, then this is useful one-liner:
DROP INDEX IF EXISTS [IndexName] ON [dbo].[TableName]
This syntax is available since SQL Server 2016. Documentation for IF EXISTS
:
In case you deal with a primery key instead, then use this:
ALTER TABLE [TableName] DROP CONSTRAINT IF EXISTS [PK_name]
Solution 5 - Sql Server
A slight deviation from the original question however may prove useful for future people landing here wanting to DROP
and CREATE
an index, i.e. in a deployment script.
You can bypass the exists check simply by adding the following to your create statement:
CREATE INDEX IX_IndexName
ON dbo.TableName
WITH (DROP_EXISTING = ON);
Read more here: CREATE INDEX (Transact-SQL) - DROP_EXISTING Clause
N.B. As mentioned in the comments, the index must already exist for this clause to work without throwing an error.
Solution 6 - Sql Server
Wrote the below function that allows me to quickly check to see if an index exists; works just like 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: This just returns the OBJECT_ID of the table, but it will be NULL if the index doesn't exist. I suppose you could set this to return index_id, but that isn't super useful.
Solution 7 - Sql Server
-- 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
Solution 8 - Sql Server
EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
GO
Solution 9 - Sql Server
To check Clustered Index exist on particular table or not:
SELECT * FROM SYS.indexes
WHERE index_id = 1 AND name IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Table_Name')