How to Rename a Table in SQL

What you’ll build or solve

You’ll rename an existing table without changing its data or structure.

When this approach works best

Renaming a table works best when you:

• Standardize inconsistent naming, such as changing user to users
• Replace temporary names like temp_orders with permanent ones
• Align table names with updated business terminology

For example, you might rename customers_old to customers_archive, or data to products.

This is a bad idea if external systems depend on the old name and you cannot update them. Renaming breaks queries, reports, and application code that still reference the original table name.

Prerequisites

• Access to the database
• Permission to modify tables
• A backup if working in production
• Basic SQL knowledge

Before renaming, confirm the table exists and that you are connected to the correct database:

SELECT table_name
FROM information_schema.tables
WHERE table_name='users';

If your system uses schemas, check the schema as well.

Step-by-step instructions

Step 1: Rename the table

Use the correct syntax for your database system.

MySQL or MariaDB

RENAMETABLE usersTO customers;

PostgreSQL or SQLite

ALTERTABLE users RENAMETO customers;

SQL Server

EXEC sp_rename'users','customers';

After running the command, the table name changes immediately. The table structure and data remain unchanged.

What to look for

• The command runs without syntax errors
• The new table name appears in your table list
• Queries using the new name work:

SELECT*FROM customers;

• Update dependent objects such as views, stored procedures, triggers, and foreign keys
• Search your application code for references to the old table name
• Test changes in staging before applying them to production

Examples you can copy

Example 1: Simple rename

You created a table with a generic name:

CREATETABLEdata (
  idINTPRIMARYKEY,
  nameVARCHAR(100)
);

Rename it:

ALTERTABLEdata RENAMETO products;

Now use the new name:

SELECT*FROM products;

Example 2: Replace a temporary name

You used a temporary table during development:

CREATETABLE temp_orders (
  order_idINT,
  totalDECIMAL(10,2)
);

Rename it when ready:

ALTERTABLE temp_orders RENAMETO orders;

The data stays intact.

Example 3: Rename in a relational setup

You have related tables:

CREATETABLE customers (
  idINTPRIMARYKEY,
  nameVARCHAR(100)
);

CREATETABLE orders (
  idINTPRIMARYKEY,
  customer_idINT,
FOREIGNKEY (customer_id)REFERENCES customers(id)
);

Rename the parent table:

ALTERTABLE customers RENAMETO clients;

After renaming, confirm that foreign keys and queries reference the updated name correctly.

Common mistakes and how to fix them

Mistake 1: Using the wrong syntax

What you might do:

RENAMETABLE usersTO customers;

Why it breaks: PostgreSQL does not support RENAME TABLE, so you get a syntax error.

Correct approach:

ALTERTABLE users RENAMETO customers;

Match the syntax to your database system.

Mistake 2: Forgetting dependent objects

What you might do: Rename the table and assume everything updates automatically.

Why it breaks: Views, procedures, and app queries may still use the old name.

Correct approach: Search for references to the old name and update them. For example, in SQL Server:

SELECT definition
FROM sys.sql_modules
WHERE definitionLIKE'%users%';

Then update affected objects.

Mistake 3: Renaming in production without preparation

What you might do: Run the rename command directly on a live database.

Why it breaks: If something fails, you risk downtime.

Correct approach: Create a backup first and test the rename in a staging environment.

Troubleshooting

• If you see “permission denied,” confirm your account has ALTER or equivalent privileges.
• If you see “relation does not exist,” verify the database and schema context.
• If queries fail after renaming, check for old table references in views, triggers, or application code.
• If foreign key errors appear, review constraints and confirm they point to the updated table name.

Quick recap

• Confirm the table exists and you have permission
• Use the correct rename syntax for your database
• Run the rename command
• Verify the new name works
• Update dependent objects and application code
• Test before and after production changes