SQL Server Rename Index

Summary: in this tutorial, you will learn how to rename an index using the system stored procedure sp_rename and SQL Server Management Studio.

Renaming an index using the system stored procedure sp_rename

The sp_rename is a system stored procedure that allows you to rename any user-created object in the current database including table, index, and column.

The statement renames an index:

EXEC sp_rename 
    index_name, 
    new_index_name, 
    N'INDEX';  
Code language: SQL (Structured Query Language) (sql)

or you can use the explicit parameters:

EXEC sp_rename 
    @objname = N'index_name', 
    @newname = N'new_index_name',   
    @objtype = N'INDEX';
Code language: SQL (Structured Query Language) (sql)

For example, the following statement renames the index ix_customers_city of the sales.customers table to ix_cust_city:

EXEC sp_rename 
        @objname = N'sales.customers.ix_customers_city',
        @newname = N'ix_cust_city' ,
        @objtype = N'INDEX';
Code language: SQL (Structured Query Language) (sql)

or in short:

EXEC sp_rename 
        N'sales.customers.ix_customers_city',
        N'ix_cust_city' ,
        N'INDEX';
Code language: SQL (Structured Query Language) (sql)

Renaming an index using the SQL Server Management Studio (SSMS)

To change the name of an index to the new one using the SSMS, you follow these steps:

First, navigate to the database, table name, and indexes:

Second, right-click on the index to which you want to change the name and choose the rename menu item. In the following picture, we will rename the index ix_customers_name of the sales.customers table:

Third, type the new name and press enter. The following picture shows the ix_customers_name index change to ix_cust_fullname:

In this tutorial, you have learned how to rename an index using sp_rename stored procedure and SQL Server Management Studio.

Was this tutorial helpful?