131

Rename Columns in SQL Server

SQL Server allows us to change the column whenever we need. We will rename the table columns when the column name is non-meaningful or does not fulfill the purpose of its creation. It must ensure that we have ALTER permission on the object before changing the column’s name.

Limitations and Restrictions

SQL Server has some restrictions while changing the column name because when we rename a column, it does not imply that all references to that column will be renamed as well. We must manually modify all objects that belong to the renamed column.

For example, if we want to change the column of a table that is also referenced in a trigger, it is required to modify the trigger for reflecting the new column name as well. The sys.sql_expression_dependencies can be used for listing all dependencies on the object before changing the name.

We can rename the table columns in SQL Server using mainly two ways:

1. Transact-SQL.
2. SQL Server Management Studio (SSMS)

Transact-SQL

SQL Server provides a standard stored procedure called SP_RENAME for changing the name of a user-created object in the current database. The user-created object can be a table, column, index, alias data type, etc.

Scripts and stored procedures may be broken when we change some portion of an object’s name. We advise you to drop the object and re-create it with the new name rather than using this statement to change the name of stored procedures, triggers, user-defined functions, or views.

The syntax to change the column name using this approach is:

Rename Column Example

The following example demonstrates the SQL Server rename column using the SP_RENAME stored procedure. To do this, we will take a ‘student’ table that contains the below data:

If we want to change the ‘city’ column with the new name ‘city_name’ of this table, we can use the above-specified SQL Server syntax or stored procedure as follows:

After executing this script, we will get the following message:

When we verify the ‘student’ table, we can see that the column name of ‘city’ is changed successfully:

SQL Server Management Studio (SSMS)

SSMS is a windows software tool used to connect and work with our SQL Server from a graphical interface instead of using the command line. The management studio allows us to rename the table columns in the following ways:

1. Rename a column using Object Explorer
2. Double click on the column name
3. Rename a column using Table Designer

Let us discuss each of them in detail.

Rename a column using Object Explorer

The following steps are used to change the column name using Object Explorer:

Step 1: Go to the Databases -> Tables menu and expand it.

Step 2: Select the desired table and expand it.

Step 3: Select the Columns and expand them.

Step 4: Right-click on the column name you want to change and choose the Rename option.

The below image explains all the above steps where we have chosen the ‘student’ table:

Step 5: Type a new name for your selected column.

Step 6: Refresh the database or table to finish the renaming steps.

Step 7: Execute the SELECT statement to verify the changed column name.

Rename a column using Table Designer

The following steps are used to change the column name using Object Explorer:

Step 1: Go to the Databases -> Tables menu and expand it.

Step 2: Select the desired table in which you want to change the column name, right-click on it, and choose the Design option from the context menu.

Step 3: Once we select the Design option, we will see the table in design mode like the below image.

Step 4: Select the column that you want to change and type the new name for this column. Let me rename the first_name column to f_name.

Step 5: To save the changes you made in the design window, click the Close button and then the Yes button in the displayed pop-up window.

We can save the changes made in the design window in another way by navigating to the File menu and click on the Save table name or press CTRL+S on the keyboard.

Step 6: Execute the SELECT statement to verify the changed column name

Double click on the column name

We can use the following steps to rename the column using a double click:

Step 1: Go to the Databases -> Tables -> Columns.

Step 2: Select the column name that you want to change and double-click. The below image explains it more clearly:

Step 3: Type a new name for your chosen column.

Step 4: Refresh the database or table to finish the renaming steps.

Step 5: Execute the SELECT statement to verify the changed column name.

NOTE: Renaming the column name using SSMS is not a good idea because it can make DBA’s work more difficult to understand while using the database tables. Therefore, it is always recommended to use T-SQL queries to make any changes in the database.

---