Rename a Table in SQL Server

In SQL Server, you can rename a table using the `sp_rename` stored procedure. This is useful when you need to change the name of a table for various reasons, such as rebranding or reorganization.

Syntax

The basic syntax for renaming a table is:

EXEC sp_rename 'old_table_name', 'new_table_name';

Where:

  • `old_table_name` is the current name of the table.
  • `new_table_name` is the new name you want to assign to the table.

Example

Let's say we want to rename the `Employees` table to `Staff`:

EXEC sp_rename 'Employees', 'Staff';

After executing this command, the `Employees` table will be renamed to `Staff`.

Important Notes

Before renaming a table, make sure to:

  • Take a backup of the database to ensure data safety.
  • Stop any active connections to the database.
  • Update any references to the old table name in your applications or scripts.

Rename a Table Using SQL Server Management Studio (SSMS)

You can also rename a table using SQL Server Management Studio (SSMS). Here's how:

  1. Open SSMS and connect to the server instance that hosts the database.
  2. In the Object Explorer, expand the database and right-click on the table you want to rename.
  3. Select Rename from the context menu.
  4. In the Rename Table dialog box, enter the new name for the table and click OK.

Result

After renaming the table, you can verify the new name by checking the table structure in SSMS or by running the following query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Staff';

This will return the new name of the table, which should be `Staff`.