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