Recently, I was working on a project to analyze the schema of a third-party vendor. In our organization, we had an internal support ticket management tool. The tool used an SQL database, and after considering the cost of the tool, we decided not to renew the contract. The plan was to build an in-house tool for internal support ticket management.

My job was to review the schema of the internal support database. The structure was very complicated, and the table names were difficult, so we could not understand what data was stored in which table. Eventually, I could identify the relationship between tables and what data was being stored in them. I have also made sure to give appropriate names to the columns so we can easily find what data is being stored in what column. I have used the sp_rename procedure to rename tables.

This article explains the basics of the renaming of a column using sp_rename stored procedure. I have also explained how to rename a column using SQL Server Management Studio. First, let us understand the basics of renaming a column.

Basics of Renaming Columns

The process of renaming the column of a table is very simple. We can use a system-stored procedure, named sp_rename. Moreover, we can also use the SQL Server Management Studio to rename a column. Note that the sp_rename stored procedure can rename the following:

• Database objects like tables, Stored procedures, and functions.
• Indexes and statistics
• User-defined datatypes.

In this article, we will learn how to rename any column of a SQL Server table using the sp_rename stored procedure.

How to Rename a Column in SQL Server

In SQL Server, to rename any column or object, we can use sp_rename stored procedure. The syntax of the sp_rename stored procedure is the following:

Exec sp_rename 'original_schema_name.original_table_name.original_column_name', 'new_column_name' ,'object_type'

In the syntax

• original_schema_name.original_table_name.original_column_name: Specify the table name whose column you want to rename. If you are renaming a column of a table that exists in the user-defined schema, you must specify the table name in three three-part names.
•  new_column_name: Specify the new name of the column.
• object_type: Specify the object type.

Let us understand the process with simple examples. Suppose you want to rename a column of patient table. The original column name is Address, and we want to change it to patient_address. The sp_rename command to rename the column is as follows.

USE HospitalManagementSystem
GO
EXEC sys.sp_rename 'patients.address','patient_address','COLUMN'

Once the column is renamed, let us verify that the column has been renamed successfully. You can run the below query to view the columns of the patient table.

use HospitalManagementSystem
go
select table_name,column_name from information_schema.columns where table_name='Patients'

We can see the output in SSMS below:

As you can see in above image, the column Address has been changed to patient_address.

Let us take another example. Suppose you want to rename the column of Sales.invoice table which exists in Wideworldimportors database. The current name of the column is InvoiceDate, and new name will be InvoiceCreateDate. The query to rename the column is following:

EXEC sys.sp_rename 'Sales.Invoices.InvoiceDate','InvoiceCreateDate','COLUMN'

Here you can see, we are changing the column name of the invoice table which is in Sales schema. Therefore, we have used three-part naming.  Once column is renamed, execute following T-SQL query to verify that the column has been renamed.

select table_name,column_name from information_schema.columns where table_name='Invoices'

Here is the output in SSMS:

Renaming Other Objects in SQL Server

The sp_rename stored procedure can be used to rename other database objects like indexes, constraints, and stored procedures. The syntax of the sp_rename procedure remains the same. The object_type argument in the sp_rename column will change. Let us see a simple example.

Suppose we want to rename the index of sales.invoice table. The current name of the index is ‘IX_Sales_Invoices_ConfirmedDeliveryTime’ and we want to change it to ‘IX_Sales_Invoices_ConfirmedDeliveryTime_New’. In the query, the value of the object_type parameter in the sp_rename procedure will be INDEX. The query to rename the index is below.

EXEC sys.sp_rename 'Sales.Invoices.IX_Sales_Invoices_ConfirmedDeliveryTime','IX_Sales_Invoices_ConfirmedDeliveryTime_New','INDEX'

Once the index is renamed, you can query sys.indexes dynamic management view to verify that index has been renamed successfully. Note that whenever we rename any index, the statistics associated with the index will be renamed as well. Here is the query to verify both changes.

SELECT object_name(object_id)[TableName], name [IndexName], Type_desc [Index Type]
FROM sys.indexes where object_id=object_id('Sales.Invoices')

Here we can see the output:

Using SQL Server Management Studio for Renaming

We can use SQL Server management studio to rename the database object. In first section, we learned the process to rename the column and indexes using sp_rename stored procedure. In this example, we will learn how to rename a constraint using SQL Server management studio. For demonstration, I will rename the constraint of Sales.invoice table. The current constraint name is DF_Sales_Invoices_InvoiceID and we will change it to Default_Sales_Invoices_InvoiceID. As name suggests, the constraint is a default constraint.

First, Open SQL Server management studio and connect to the database server. Expand databases → Expand Wideworldimportors database.

You can see a lot of tables in a database. Expand Sales.invoice table → Expand Constrints. → Hit F2 or right-click on DF_Sales_Invoices_InvoiceID and select Rename.

The name will be editable in the Object Explorer. Change the name to Default_Sales_Invoices_InvoiceID and hit enter. The name will be changed. SSMS prompts a confirmation message which looks like following image.

Click Yes to change the name. Once changes are made, execute following T-SQL query to verify that constraint has been renamed successfully.

SELECT
    [constraint].name AS constraint_name,
    OBJECT_NAME([constraint].parent_object_id) AS table_name,
    [column].name AS column_name from
    sys.default_constraints [constraint]
JOIN
    sys.columns [column] ON [constraint].parent_object_id = [column].object_id
    AND [constraint].parent_column_id = [column].column_id
    where  OBJECT_NAME([constraint].parent_object_id)='Invoices'

We can see this in the SSMS output:

Let us take a look at some limitations and things to be considered before renaming any column.

Limitations and Considerations

If you are renaming any column in a table or renaming any object in a database, you must consider the following limitations and possible issues that might break the application.

1. ALTER permission is needed on the object that you want to rename. Suppose you want to rename a column name; you must have ALTER object permission on the table whose column you are renaming.
2. Renaming a column name always breaks the stored procedure or other objects (View, function, etc.) that are referencing that column. For example, you are renaming a column that is being used in a view. Therefore, make sure you modify all the stored procedures, functions, and triggers that are referencing the column that was renamed. You can use sys.sql_expression_dependencies to find all dependencies of the column.
3. When you rename a stored procedure, the object's name in sys.sql_modules will not change. Hence Microsoft recommends dropping and recreating an object instead of just renaming it.
4. When you rename a column of a table which is part of replication, the replication might break. To  so if we want to rename the column of the replicated table, first, we must pause the replication, then rename the column using sp_rename or SQL Server management studio, update all database objects that are referencing the column, and finally, re-initialize replication with the new snapshot.

Conclusion

In this article, we learned about the process of renaming any column in a table. I have explained how to rename a column using a system-stored procedure named sp_rename with syntax and simple examples. Moreover, we also learned how to rename the column using SQL Server Management Studio. Note that we can also use dbForge Studio for SQL Server to execute the stored procedure to rename a column. Also, we discussed the limitations and other issues that must be taken care of before renaming any object or column.