I recently worked on a project to assess the schema of a third-party vendor. Our organization has an internal support ticket tracking tool. The program used a SQL database, and after calculating its cost, we opted not to extend the contract. The objective was to create an in-house platform for managing internal support tickets.
My responsibility was to review the schema of the internal support database. We couldn't figure out what data was in which table because the structure was intricate and the table names were tough. Eventually, I was able to determine the relationship between tables and what data was contained in each. I've also given the columns proper names so that we can quickly identify what data is contained in which column. I used the sp_rename method to rename tables.
This article explains how to rename a column using the sp_rename stored procedure. I also demonstrated how to rename a column in SQL Server Management Studio. First, let us look at the fundamentals of renaming a column.
The Basics of Renaming Columns
Renaming a table's column is a simple task. We can use a system-stored process called sp_rename. Additionally, we can utilize SQL Server Management Studio to rename a column. 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 an SQL Server table using the sp_rename stored procedure.
How can you rename a column in SQL Server?
In SQL Server, we may rename any column or object using the sp_rename stored procedure. In this post, we'll look at how to rename columns using the sp_rename function.
The syntax for the sp_rename stored procedure is as follows.
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 the 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'
Output
As you can see in the 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 the Wideworldimportors database. The current name of the column is InvoiceDate, and the new name will be InvoiceCreateDate. The query to rename the column is the following.
EXEC sys.sp_rename 'Sales.Invoices.InvoiceDate','InvoiceCreateDate','COLUMN'
Here you can see, that we are changing the column name of the invoice table which is in the Sales schema. Therefore, we have used three-part naming. Once the column is renamed, execute the 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'
Output
Renaming other objects in SQL Server
The sp_rename stored procedure can be used to rename other database objects, such as indexes, constraints, and stored procedures. The syntax of the sp_rename operation stays unchanged. The object_type argument for the sp_rename column will change. Let us consider a basic example.
Assume we wish to rename the index of the sales invoice table. The index's present name is 'IX_Sales_Invoices_ConfirmedDeliveryTime', which we would like to modify to 'IX_Sales_Invoices_ConfirmedDeliveryTime_New'. In the query, the object_type argument in the sp_rename method will be set to INDEX. The query to rename the index is shown 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 the 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')
Output
Using SQL Server Management Studio to Rename
We can use SQL Server Management Studio to rename the database object. In the first section, we learnt how to rename columns and indexes using the sp_rename stored procedure.
In this example, we'll see how to rename a constraint in SQL Server Management Studio. For demonstration, I'll rename the constraint in the Sales.invoice table. The present constraint name is DF_Sales_Invoices_InvoiceID, which we will rename to Default_Sales_Invoices_InvoiceID. As the name implies, this constraint is a default constraint.
First, launch SQL Server Management Studio and connect to your database server. Expand databases. Expand the Wideworldimportors database.
A database contains many tables. Expand the Sales, Invoice, and Constraint tables. Press F2 or right-click on DF_Sales_Invoices_InvoiceID and choose Rename.
The name will be editable. Change the name to Default_Sales_Invoices_InvoiceID and hit enter. The name will be changed. The SQL Server management studio prompts a confirmation message that looks like the following image.
Click OK to change the name. Once changes are made, execute the following T-SQL query to verify that the 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'
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.
- 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.
- 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 reference the column that was renamed. You can use sys.sql_expression_dependencies to find all dependencies of the column.
- 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.
- When you rename a column of a table that is part of replication, the replication might break 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, reinitialize replication with the new snapshot.
Conclusion
In this tutorial, we learned how to rename any column in a table. I demonstrated how to rename a column using a system-stored process called sp_rename, complete with syntax and easy examples. We also learned how to rename a column using SQL Server Management Studio. We can also use other tools, such as dbForge Studio for SQL Server, to run the stored procedure to rename a column. We also reviewed the limits and other difficulties that must be addressed before to renaming any object or column.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
