Smart Rename

The Smart Rename refactoring generates a script to rename objects in your database without breaking dependencies so that the integrity of your database is maintained. You can also use this feature to change the object owner.

You can rename the following:

• database objects:
  • tables
  • views
  • stored procedures
  • user-defined functions
• parameters in:
  • stored procedures
  • user-defined functions
• columns in:
  • tables
  • views

You can use Smart Rename to rename encrypted objects in SQL Server 2000 if you are a member of the sysadmin server role; you cannot use this feature to rename encrypted objects in SQL Server 2005.

You are advised to back up your database prior to running the rename script.

When an object is renamed, SQL Refactor:

• changes the definition

  When you run the Smart Rename script, the object definition is modified and SQL Refactor ensures that the sys.sql_modules table (for SQL Server 2005) or the syscomments table (for SQL Server 2000) remains consistent.

• modifies any objects that reference the renamed object so that dependencies are not broken

  The following referencing objects are modified if necessary:

  • procedures
  • views
  • functions
  • DML triggers
  • DDL triggers
  • queues (if the owner or name of a stored procedure is changed)
  • defaults (if the owner or name of a function is changed)
  • computed columns (if the owner or name of a function is changed)
• changes self-referencing (for recursive stored procedures and functions)
• fixes the name of the object to be renamed, if necessary

  If you have previously renamed an object using SQL Server Management Studio or Enterprise Manager Rename or the Transact-SQL sp_rename command, the object definition will contain the original name. SQL Refactor fixes the object definition so that it is consistent with the new name. For more information about the sp_rename command, see SQL Server Books Online.

  Note that any objects that reference the original name (the name prior to using sp_rename) are not updated.

• fixes the name of any referencing objects that are to be modified, if necessary

  If you have previously renamed a referencing object using SQL Server Management Studio Rename or the Transact-SQL sp_rename command, the object definition will contain the original name. SQL Refactor fixes the object definition of the referencing object so that it is consistent with its current name.

The original permissions and extended properties of the object are preserved.

To rename an object:

1. On the SQL Refactor menu click Smart Rename, or in the Object Explorer pane, right-click the object and click Smart Rename.

   The Smart Rename dialog box is displayed.

   

2. If you want to change the object owner, select the name of the new owner.
3. If you want to change the object name, type the new name.
4. Click Next.

   SQL Refactor checks that the name is not already in use. If the name is valid, SQL Refactor generates the script and displays summary information:

   

   • Action Plan is a summary of the actions that the script will perform, in the order in which they will occur.
   • Warnings displays information that you should consider prior to running the script, and reasons the script might fail.

     The warnings are graded according to severity.

   • Referencing Objects lists the objects that will be modified by the script because they reference the object that is being renamed.

     The action plan and warnings are included in the generated script, below the header. If you do not want to include this information in the script, clear the Include summary in the generated script check box.

5. Click View Script.

   The Smart Rename dialog box is closed, and the SQL script is displayed in the SQL Server Management Studio query editor. Review the script, and then run it to rename the object and modify the referencing objects.

Note that when you run the script, the Object Explorer is not updated with the new name. You must refresh the list to update the name that is displayed.