Renaming a Table With Almost No Downtime Using sp_rename

Renaming a table with sp_rename is a quick way to swap in a prepared replacement, but a rename moves only the name. Indexes, permissions and foreign keys stay with the object they belong to. Load the new table first, swap the names in one short transaction, and check what stayed behind.

The midnight swap

You need to replace a big table with a rebuilt copy. Loading it in place would lock everyone out for an hour. So you load a new table beside it, in the background. At midnight you only swap the names. That swap takes a blink, which is where the “almost no downtime” comes from.

The trick has limits. A rename needs a schema lock, so it waits behind queries that are already using the table. It also does not move anything but the name. We will see both sides. The demo creates a few small tables and drops them at the end.

Prepare both tables outside the swap

Here are the live table and its replacement, each with one row so you can tell them apart. I also add a child table that points at the live one with a foreign key, and a role that may read it. At the end of the block I save each table’s object ID, which is the identity SQL Server really tracks.

DROP TABLE IF EXISTS dbo.OrderLines;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.OrdersNext;
DROP TABLE IF EXISTS dbo.OrdersOld;
DROP TABLE IF EXISTS #Before;
DROP ROLE IF EXISTS DemoReaders;

CREATE ROLE DemoReaders;
CREATE TABLE dbo.Orders     (OrderId int NOT NULL PRIMARY KEY, Note nvarchar(40));
CREATE TABLE dbo.OrdersNext (OrderId int NOT NULL PRIMARY KEY, Note nvarchar(40));
CREATE TABLE dbo.OrderLines
(
    LineId  int NOT NULL PRIMARY KEY,
    OrderId int NOT NULL CONSTRAINT FK_OrderLines_Orders REFERENCES dbo.Orders (OrderId)
);

INSERT dbo.Orders     VALUES (1, N'Existing value');
INSERT dbo.OrdersNext VALUES (1, N'Replacement value');
INSERT dbo.OrderLines VALUES (10, 1);
GRANT SELECT ON dbo.Orders TO DemoReaders;

SELECT name, object_id INTO #Before
FROM sys.tables WHERE name IN (N'Orders', N'OrdersNext');

SELECT name, object_id FROM #Before ORDER BY name;

Matching column names are not enough in real life. The replacement also needs the same indexes, nullability, constraints, triggers and permissions. Check all of that before the window, not during it.

Swap the names in one transaction

Both renames go inside one transaction, so nobody sees a moment where the table has no name. SET LOCK_TIMEOUT 5000 means “wait at most five seconds for the lock, then give up”. If anything fails, the catch block rolls back whatever was renamed, and the error comes up so your job sees it.

SET XACT_ABORT ON;
SET LOCK_TIMEOUT 5000;

BEGIN TRY
    BEGIN TRANSACTION;
    EXEC sys.sp_rename N'dbo.Orders',     N'OrdersOld', N'OBJECT';
    EXEC sys.sp_rename N'dbo.OrdersNext', N'Orders',    N'OBJECT';
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    SET LOCK_TIMEOUT -1;
    THROW;
END CATCH;

SET LOCK_TIMEOUT -1;

SQL Server prints a caution twice, “Changing any part of an object name could break scripts and stored procedures”. That is a polite warning that nothing but the name is rewritten. Plan the retry before the window, because a lock timeout means the swap did not happen.

Check which object kept which identity

Now compare names with the object IDs we saved. The join is on the object ID, so it shows each table’s current name next to the name it had before.

SELECT t.name AS current_name, b.name AS name_before
FROM sys.tables AS t
JOIN #Before AS b ON b.object_id = t.object_id
ORDER BY t.name;

SELECT OrderId, Note FROM dbo.Orders ORDER BY OrderId;

The table now called Orders was OrdersNext, and OrdersOld was Orders. Each object ID stayed with its table, and only the names moved. Selecting from dbo.Orders returns the replacement value, so the application sees the new data.

Follow dependencies by identity

Here is the catch. The foreign key and the permission were attached to the original table, and that table now has a new name. Look at where each one points.

SELECT fk.name AS foreign_key,
       OBJECT_NAME(fk.parent_object_id)     AS child_table,
       OBJECT_NAME(fk.referenced_object_id) AS parent_table
FROM sys.foreign_keys AS fk;

SELECT OBJECT_NAME(p.major_id) AS object_name,
       USER_NAME(p.grantee_principal_id) AS grantee, p.permission_name
FROM sys.database_permissions AS p
WHERE p.class = 1 AND p.grantee_principal_id = DATABASE_PRINCIPAL_ID(N'DemoReaders');

The key still points at OrdersOld, not at the new Orders. The SELECT permission for DemoReaders is also on OrdersOld, so the new Orders has none. The rename does not rewrite stored code or application strings either. Make a list of every dependency before the swap. Then test the real application calls, because dynamic SQL and outside tools may not show up in a dependency list.

Clean up, and keep the old table until you are sure

A rename needs a plan for writes during the cutover, and a rehearsal of the lock wait and the rollback. In a real swap, keep the old table until you trust the new one. Dropping it is a separate decision. In this demo we remove everything.

DROP TABLE IF EXISTS dbo.OrderLines;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.OrdersOld;
DROP TABLE IF EXISTS #Before;
DROP ROLE IF EXISTS DemoReaders;

Rehearse the swap and the dependencies before anyone is waiting for the cutover.

A table rename is not a dependency transfer, it is a new name for the same object.

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