Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "sp_rename ... 'OBJECT'" - Renaming Existing Tables

By: FYIcenter.com

(Continued from previous topic...)

How to rename an existing table with the "sp_rename" stored procedure?

If you have an existing table and you want to change the table name, you can use the "sp_rename ... 'OBJECT'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables). The tutorial example below shows you how to rename a table:

sp_rename 'tip', 'faq', 'OBJECT' 
GO
Caution: Changing any part of an object name could break 
   scripts and stored procedures.

SELECT name, type_desc, create_date FROM sys.tables
GO
name      type_desc       create_date
faq       USER_TABLE      2007-05-19 23:05:43.700
tipBackup USER_TABLE      2007-05-19 23:25:23.357

You can also rename a table with on the Object Explorer window of SQL Server Management Studio. See tutorials on rename table columns.

(Continued on next topic...)

1. What is a table?
2. What are DDL (Data Definition Language) statements for tables?
3. How to create new tables with "CREATE TABLE" statements?
4. How To Get a List of All Tables with "sys.tables" View?
5. How To Get a List of Columns using the "sys.columns" View?
6. How To Get a List of Columns using the "sp_columns" Stored Procedure?
7. How To Get a List of Columns using the "sp_help" Stored Procedure?
8. How To Generate CREATE TABLE Script on an Existing Table?
9. How to create new tables with "SELECT ... INTO" statements?
10. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
11. How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
12. How to rename an existing column with the "sp_rename" stored procedure?
13. How to rename an existing column with SQL Server Management Studio?
14. How to change the data type of an existing column with "ALTER TABLE" statements?
15. How to rename an existing table with the "sp_rename" stored procedure?
16. How To Drop an Existing Table with "DROP TABLE" Statements?