Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.
Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter ... rename statements for indexes and triggers.
The problem is the fact that this change is not propagated to other objects that depend on the renamed object - they become invalid and return an error when used. To avoid this, all objects that depend on the object that is changed need to be modified.
How to find the objects that depend on the renamed object
SQL Server Management Studio can track dependencies and show the objects that depend on the selected one through its View Dependencies option.
For a large number of objects, this can take quite a while and like every manual process, it is prone to mistakes.
How to rename an object without too much trouble
ApexSQL Search is a SQL Server Management Studio and Visual Studio add-in capable of smart renaming objects like tables, views, procedures and functions, as well as table/view columns and function/procedure parameters without breaking database dependencies.
To smart rename an object:
Click the Preview button:
The Preview section displays summary information:
If there is need to modify the script before execution, click the Create script button. The generated script will be opened in the SQL Server Management Studio Query editor/Visual Studio editor.
Review, modify if needed and execute it with a click on the Execute option, or save it to a file.
Renaming an object can be easy. There is no need to analyze a whole database and search for dependencies yourself. Use free SQL database tool ApexSQL Search to do that. It will find all dependent objects, rename and drop–recreate when necessary, and avoid breaking the scripts.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center