Rename a Column or Table in SQL Server Safely

To rename a column in SQL Server, call sp_rename with the old name, the new name and the word COLUMN. The statement takes a second. The work is in finding everything that still uses the old name.

A Demo With Something to Break

The first script creates a database named RenameObjectDemo. It holds a Guests table with three rows and a primary key. A view reads that table, and a database user can query it. The user has no login, so nothing is added to the server.

IF DB_ID(N'RenameObjectDemo') IS NULL CREATE DATABASE RenameObjectDemo;
GO
USE RenameObjectDemo;
GO
DROP VIEW IF EXISTS dbo.GuestList;
DROP TABLE IF EXISTS dbo.Guests, dbo.Visitors, dbo.Pairs;
CREATE TABLE dbo.Guests (GuestID int NOT NULL CONSTRAINT PK_Guests PRIMARY KEY, OldName nvarchar(40) NOT NULL, City nvarchar(40) NOT NULL);
INSERT INTO dbo.Guests (GuestID, OldName, City) VALUES (1, N'Maya Collins', N'Portland'), (2, N'Leo Brennan', N'Austin'), (3, N'Priya Shah', N'Denver');
GO
CREATE VIEW dbo.GuestList AS SELECT GuestID, OldName FROM dbo.Guests;
GO
IF USER_ID(N'GuestReaderUser') IS NULL CREATE USER GuestReaderUser WITHOUT LOGIN;
GRANT SELECT ON dbo.Guests TO GuestReaderUser;

Look Before You Rename

Ask SQL Server what depends on the table before you change it. This function lists every object that refers to it. It reads the dependency records, so it can’t see application code, reports or text inside a string.

SELECT referencing_entity_name FROM sys.dm_sql_referencing_entities(N'dbo.Guests', N'OBJECT');

One view depends on the table. That’s the first thing the rename will break. The list is a starting point, not a guarantee. Search your application code and reports for the old name too.

Rename the Column

To rename a column, the procedure takes three values. The first is the current name of the column, written with its table. The second is the new name alone. The third is the word COLUMN, so SQL Server knows what you are renaming.

EXEC sp_rename N'dbo.Guests.OldName', N'FullName', N'COLUMN';

SQL Server prints a caution on the Messages tab. It reads: Caution: Changing any part of an object name could break scripts and stored procedures.

The caution is a message, not an error, and the rename has already happened. The next query reads the table under the new name.

SELECT GuestID, FullName FROM dbo.Guests ORDER BY GuestID;

What the Rename Left Behind

The view still contains the old name. SQL Server renamed the column in the table, and it left the view’s text alone.

SELECT * FROM dbo.GuestList;

Msg 207, Level 16, State 1, Procedure GuestList, Line 1
Invalid column name 'OldName'.

A second message, 4413, adds that the view has binding errors. SSMS 22 adds [Batch Start Line 0] to message 207. It prints Line 461 for message 4413, as the picture shows. Nothing warned you about this at rename time. A view, a procedure or a report that uses the old name breaks the next time it runs. The fix is to alter the view, because renaming doesn’t rewrite object text.

ALTER VIEW dbo.GuestList AS SELECT GuestID, FullName FROM dbo.Guests;
GO
SELECT * FROM dbo.GuestList ORDER BY GuestID;

Rename a Table

The table works the same way, with one difference: you leave out the third value. The old name stops working at once. The view breaks again, because the table it reads has a new name. Alter the view again to point at dbo.Visitors.

EXEC sp_rename N'dbo.Guests', N'Visitors';
GO
SELECT COUNT(*) AS ByOldName FROM dbo.Guests;

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Guests'.

The primary key constraint is a leftover too. Its name still says Guests. SQL Server doesn’t rename indexes, constraints or defaults when you rename their table or column. They keep working, but they confuse the next person who reads an execution plan or an error message. Rename them with the object type OBJECT.

EXEC sp_rename N'dbo.PK_Guests', N'PK_Visitors', N'OBJECT';
GO
SELECT name FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.Visitors');

Permissions Stay With the Table

A common worry is that a rename loses the permissions. It doesn’t. Permissions belong to the object, not to its name. The next query shows the grant on the renamed table.

SELECT pr.name AS UserName, OBJECT_NAME(pe.major_id) AS ObjectName, pe.permission_name, pe.state_desc
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pe.major_id = OBJECT_ID(N'dbo.Visitors');

The user still has SELECT. This is the reason to rename instead of creating a new table with the old name and copying the rows. A new table starts with no permissions, and you must grant them again.

Swap Two Column Names

Sometimes two names must trade places. You can’t rename A to B while B exists. Use a temporary name. Wrap the three renames in one transaction, so a failure leaves the table as it was.

CREATE TABLE dbo.Pairs (A int NOT NULL, B int NOT NULL);
INSERT INTO dbo.Pairs (A, B) VALUES (1, 2);
GO
BEGIN TRANSACTION;
EXEC sp_rename N'dbo.Pairs.A', N'A_old', N'COLUMN';
EXEC sp_rename N'dbo.Pairs.B', N'A', N'COLUMN';
EXEC sp_rename N'dbo.Pairs.A_old', N'B', N'COLUMN';
COMMIT TRANSACTION;
SELECT A, B FROM dbo.Pairs;

The values stay with their columns, so the column that held 1 is now called B. A related trap is the new name. Pass the name alone. If you write N'Pairs.X' as the second value, SQL Server creates a column named Pairs.X, prefix included.

Renaming in SSMS, and When Not to Rename

In SSMS 22, select the table or column in Object Explorer. Press F2, or right-click and choose Rename. The result is the same as the procedure. The dependencies are exactly the same too, so look first.

You could argue that a rename is only a name, and the old name was a mistake worth fixing. That’s fair for a table nobody else uses. For a table that applications, reports and spreadsheets read, the rename is a change to a shared contract. Then add a view with the old name. The old callers keep working while you update them one by one.

What to Remember

List the dependents before you rename a column or a table. Rename with sp_rename, then alter the views, procedures and reports that used the old name. Rename the constraints and indexes that still carry it. Permissions stay, and the data stays. When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'RenameObjectDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RenameObjectDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RenameObjectDemo;
END;

A rename is not a small change, it is a promise to find every place that used the old name.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.