Published 11 February 2013
SQL Prompt can create a script that allows you to rename objects in your database without breaking dependencies. You can rename the following:
- Tables (including columns)
- Views (including columns)
- Stored procedures (including parameters)
- Functions (including parameters)
When an object is renamed:
- SQL Prompt also modifies any objects that reference, or are referenced by, the renamed object to ensure that dependency links are not broken.
If you have previously renamed an object using SQL Server Management Studio or Enterprise Manager Rename, or the T-SQL sp_rename command, the object definition will contain the original name.
Any objects that reference this original name are not updated.
To help you locate objects that reference objects that no longer exist, see Finding invalid objects.
- The original permissions and extended properties of the object are preserved.
You can rename encrypted objects in SQL Server 2000 if you are a member of the sysadmin server role; you cannot rename encrypted objects in SQL Server 2005 or later.
To rename an object:
- On the SQL Prompt menu, click Smart Rename, or in the SQL Server Management Studio Object Explorer pane, right-click the object you want to rename and select Smart Rename.
The Smart Rename dialog box is displayed:
- If you want to change the object owner, select a new owner.
- Type a new name for the object.
The name you enter must not already be in use.
- Click Next.
SQL Prompt creates the rename script and displays summary information:
- Actions 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, including any reasons the script might fail.
- Dependencies lists objects that will be modified by the script because they reference, or are referenced by, the primary table.
Click View Script.
The dialog box is closed, and the rename script is displayed in a new SQL Server Management Studio query window.We recommended you back up your database before you run the script.
- Once you have reviewed the script, run it.
You must refresh the Object Explorer list before it will display the new name.