Programatically renaming a table in SQL Server 2000 - is sp_rename the only solution?

Question

I recently had to rename a table (and a column and FK/PK contraints) in SQL Server 2000 without losing an data. There did not seem to be an obvious DDL T-SQL statements for performing this action, so I used sp_rename to directly fiddle with object names.

Was this the only solution to the problem? (other, than give the table the correct name in the first place - doh!)

Answer 1

sp_rename is the correct way to do it.

EXEC sp_rename 'Old_TableName', 'New_TableName'

Answer 2

Maybe not the only: I guess you could always toy with the master database and update the table name there - but this is highly unrecommendable.

Answer 3

There is a solution that can let you work concurrently with both old and new versions of the table. This is particularly important if your data is replicated and/or is accessed through client interface (meaning old versions of the client interface will still work with the old table name):

1. Modify the constraints (including FKs) on your table through "ALTER TABLE" command

2. Do not change table name or field name but create a view such as:

   SELECT oldTable.oldField1 as newField1, ...

   save it as newTable (and, if requested, distribute it on your different servers)

---

Note that you cannot modify your PK this way.

Answer 4

Ya
EXEC sp_rename 'Old_TableName', 'New_TableName' work fine but are any key word like "alter tabel old_name to new_name "