Using sp_rename to Change Identifiers

The system procedure sp_rename renames tables, indexes, views, procedures, triggers, rule, defaults, user-defined datatypes, and columns. Use sp_renamedb to rename databases.

The types of identifiers that you can change with sp_rename and the changes you need to make on the server and in your application programs are:

  • Table name:

    • Drop all procedures, triggers and views that reference the table, and re-create them with the new name. Use sp_depends to find the objects that depend on the table.

    • Change all applications or SQL source scripts that reference the table to use the new table name.

    • Change dbcc scripts that perform table-level checks using table names.

  • Index name:

    • Drop any stored procedures that create or drop the index, and re-create them with the new name.

    • Change all applications or SQL source scripts that create or drop the index.

    • Change dbcc scripts that perform index-level checks using index names.

  • View name:

    • Drop all procedures, triggers, and views that reference the view, and re-create them with the new name. Use sp_depends to find the objects that depend on the view.

    • Change all applications or SQL source scripts that reference the view to use the new view name.

  • Procedure name:

    • Drop and re-create with the new procedure name all procedures and triggers that reference the procedure.

    • Change all applications or SQL source scripts that execute the procedure to use the new name.

    • If another server remotely calls the procedure, change applications on the remote server to use the new procedure name.

  • Trigger name – change any SQL source scripts that create the trigger.

  • Rule name – change any SQL source scripts that create the rule.

  • Default name

    Change any SQL source scripts that create the default.

  • User-defined datatype name

    • Drop all procedures that create tables with user-defined datatypes, and re-create them with the new name.

    • Change any applications that create tables with user-defined datatypes.

  • Column name:

    • Drop all procedures, triggers and views that reference the column, and re-create them with the new column name.

      sp_depends cannot find column name references. The following query displays the names of procedures, triggers, and views that reference a column named “key”:

      select distinct sysobjects.name
      from sysobjects, syscomments
      where sysobjects.id = syscomments.id
      and syscomments.text like "%key%"
    • Change all applications and SQL source scripts that reference the column by name.

To change the name of the view isolation to isolated, use:
sp_rename "isolation", isolated
To change the name of a column in the renamed view isolated, use:
sp_rename "isolated.key", keyname
Use sp_depends to get a list of all views, procedures, and triggers that references a view, procedure, or table that is renamed. To use sp_depends after renaming an object, give the new name. For example:
sp_depends new_name