{"id":555,"date":"2018-05-27T20:47:50","date_gmt":"2018-05-27T13:47:50","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=555"},"modified":"2020-07-11T03:20:50","modified_gmt":"2020-07-10T20:20:50","slug":"sql-server-rename-table","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-rename-table\/","title":{"rendered":"SQL Server Rename Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to rename a table using Transact SQL and SQL Server Management Studio.<\/p>\n\n\n\n

SQL Rename table using Transact SQL #<\/a><\/h2>\n\n\n\n

SQL Server does not have any statement that directly renames a table. However, it does provide you with a stored procedure named sp_rename<\/code> that allows you to change the name of a table.<\/p>\n\n\n\n

The following shows the syntax of using the sp_rename<\/code> stored procedure for changing the name of a table:<\/p>\n\n\n

EXEC sp_rename 'old_table_name', 'new_table_name'\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that both the old and new name of the table whose name is changed must be enclosed in single quotations.<\/p>\n\n\n\n
Let’s see the following example.<\/p>\n\n\n\n
First, create a new table named sales.contr<\/code> for storing sales contract’s data:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.contr (\n    contract_no INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    expired_date DATE<\/span>,\n    customer_id INT<\/span>,\n    amount DECIMAL<\/span> (10<\/span>, 2<\/span>)\n); \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the sp_rename<\/code> stored procedure to rename the sales.contr<\/code> table to contracts<\/code> in the sales<\/code> schema:<\/p>\n\n\n
EXEC sp_rename 'sales.contr', 'contracts';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server returns the following message:<\/p>\n\n\n
Caution: Changing any part of an object name could break scripts and stored procedures.<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
However, it renamed the table successfully.<\/p>\n\n\n\n
SQL Server rename table using SSMS #<\/a><\/h2>\n\n\n\n
Another way to rename a table is to use the function provided by SQL Server Management Studio.<\/p>\n\n\n\n
The following example illustrates how to rename the product_history<\/code> table to product_archive<\/code>.<\/p>\n\n\n\n
First, right-click on the table name and choose Rename menu item:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Second, type the new name of the table e.g., product_archive<\/code> and press Enter:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this tutorial, you have learned how to rename a table in a database using the sp_rename<\/code> stored procedure and SQL Server Management Studio.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t