Renaming Tables

If you need to change the name of a table, you can rename it.

    To rename a table

• Execute the sp_rename system stored procedure.

  For example:

  sp_rename titles, books

  renames the titles table to the books table.

  To change back to titles, run:

  sp_rename books, titles

You can also use sp_rename to rename other database objects ¾ views, indexes, rules, defaults, procedures, and triggers. You must be the owner of an object to rename it.

Procedures, triggers, and views that depend on a renamed object (table) work fine until they are recompiled. When the procedure, trigger, or view that depends on a renamed object is recompiled by SQL Server, it will no longer work.

To avoid this problem, the user must change the procedure, trigger, or view text to reflect the new name of the object on which it depends. The old object name appears in query results until the procedure, trigger, or view has been changed and recompiled. The safest course is to change the definitions of any dependent objects when you execute sp_rename.

When you rename a table, the name of the table is changed in the sysobjects system table.