This article will show a couple of ways of how to determine SQL object dependencies between SQL Server objects and how to safely rename a SQL Server object without compromising referenced integrity and break dependencies between objects using the ApexSQL Fundamentals Toolkit for SQL Server pack.
ApexSQL Fundamentals Toolkit for SQL Server pack consists of the following tools:
- ApexSQL Analyze
- ApexSQL Complete
- ApexSQL Decrypt
- ApexSQL Propagate
- ApexSQL Refactor
- ApexSQL Script
- ApexSQL Search
View SQL object dependencies
Understanding dependencies between objects can be very useful in a situation like renaming, updating, or deleting objects that depend upon other objects.
In the situation when for some reason (e.g. typographical error, etc.), a table or table column name in a database needs to be changed. But before renaming, you need to know if that table or table column is used elsewhere (e.g. views, stored procedures, etc.) so that renaming will not damage referenced integrity.
Or in the situation when you ask to identify cross-database dependencies and find the objects referenced by other databases.
There are several methods to get information about SQL object dependencies.
The first method is by using the SQL Server Management Studio (SSMS) feature called View Dependencies. In Object Explorer, right-click on the object of interest, and, from the context menu, choose the View Dependencies command:
By default, on the Object Dependencies window, the objects that are dependent on the chosen table will be shown:
When the second radio button is selected, in the grid, the objects that the chosen table depends upon will be shown:
The second approach is to use the system stored procedure sp_depends to show object types views and stored procedures that depend on a table or view, and the tables and views that are dependent on the view or stored procedure:
EXEC sp_depends @objname = N'Person.Address';
When run, the following information will appear:
Also, the syscomments view can be used to list all views, stored procedure, etc. that depend on a chosen table:
SELECT DISTINCT syso.name FROM syscomments sysc INNER JOIN sysobjects syso ON sysc.id = syso.id WHERE charindex('Address', TEXT) > 0
Similar to the syscomments view, the INFORMATION_SCHEMA.ROUTINES view can be used to search through the definition of the routine such as functions, stored procedures, etc.:
SELECT routine_name ,routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Address%';
One more method to determine SQL object dependencies is to use the undocumented SQL Server stored procedure sp_msdependencies.
To view the first level of objects that are dependent on the specified object, run the following query:
EXEC sp_MSdependencies N'Person.[Address]' ,NULL ,1315327;
The result will be:
To see all levels of dependencies for the objects that are dependent on the specified object, run the following code:
EXEC sp_MSdependencies N'Person.[Address]' ,NULL ,266751;
To be able to see the objects on which the specified object is dependent, run the following command:
EXEC sp_MSdependencies N'Person.[Address]' ,NULL ,1053183;
More about object dependencies can be found on the How to create a SQL dependency diagram in SQL Server page.
For the above-mentioned situations, to get a clear picture of which objects depend on the chosen object and on which objects a chosen object depends, you need to run a few different methods.
In a situation like this, a graphical SQL object dependencies viewer like ApexSQL Analyze can be of great help in finding cross-database dependencies or specific table or column dependencies. All objects in ApexSQL Analyze are present with different shapes and colors to improve their visual look and readability.
To see database object dependencies, under the Home tab, click the New button and on the Connection to SQL Server window, set credentials to connect to SQL Server, and from the Database drop-down list, choose a database:
When all is set, click the OK button. Under the Dependency viewer window all object and dependencies between the objects will be shown:
In the Object browser pane, find the object of interest, double click on it, and in the view grid, that object will be selected with all dependencies, all other objects will be grayed out:
In the Dependencies pane, all objects on which the chosen object depends will be listed and all objects which depend on the chosen object:
Additionally, the level of dependencies depth can be set in the Dependencies depth field under the General tab:
SQL Server safe object renaming
Even though all the SQL object dependencies were determined using ApexSQL Analyze, creating a script for renaming an object which will update all other referenced objects and doing so not to break some dependencies between them can be a very challenging job.
Using the Rename command from SSMS is something that won’t help much, even doing more harm than good, and eventually, if you rename some object (e.g. Address table) you can end up with a mistake like the one below when executing some views, stored procedures, etc. that depended on the Address table:
Msg 208, Level 16, State 1, Procedure vSalesPerson, Line 34 [Batch Start Line 0]
Invalid object name ‘Person.Address’.
Msg 4413, Level 16, State 1, Line 24
Could not use view or function ‘AdventureWorks2017.Sales.vSalesPerson’ because of binding errors.
Completion time: 2020-11-23T15:24:41.3589340+01:00
Creating the script for renaming an object from scratch on which a code for modification of all dependence’s objects will be included can be a time-consuming job, and with little success to work on the first run.
The ApexSQL Fundamentals Toolkit for SQL Server pack comes with the features that solve that problem. The Safe rename feature comes with the installation of ApexSQL Refactor or ApexSQL Search add-ins. The Safe rename feature creates a renaming script for the chosen object and then when the script is run will modify all objects’ dependencies to reflect the change.
To initiate this feature, in Object Explorer, select an object that wants to be renamed, right-click and, from the context menu, choose the Safe rename command:
The Safe rename window will appear. Here, in the New name field, enter a new name for the object and click the Preview script button:
In the Generated script tab, a generated T-SQL script will be shown that will rename the chosen object with all other dependent objects.
To review or modify the generated T-SQL script, click the Create script button. The new query editor will be opened with all renaming code in it.
Now, all that needs to be done is to click the Run button in SSMS or Visual Studio and the object will be renamed successfully without breaking any SQL object dependencies:
Conclusion
With ApexSQL Fundamentals Toolkit for SQL Server pack and tools that it provides all SQL object dependencies related to an object can be determined and safely renamed without typing any line of code.
This example showed how to view dependencies for a table and rename it without breaking any dependencies, but the same steps can be applied to any SQL Server database objects (table/view columns, functions/procedures parameters, views, functions, procedures).
November 30, 2020
