Ben Richardson

Identifying Object Dependencies in SQL Server Management Studio

July 16, 2018 by

In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects.

Consider a scenario where a table has a one to many relationships with another table. Before deleting or modifying the table, you should know which objects are dependent on this table and what impact deleting the table will have on the dependent objects.

In this article, we will see how SQL Server management studio can be used to Identify dependencies between database objects.

Preparing Dummy Data

Execute the following script to create a dummy database.

In the script above, we create a database named “Library”. We then create two tables within the library database, “Author” and “Book”. The Author table has a one to many relationships with the Book table since the book table has a foreign key column author_id which references the id column of the Author table. In short, Book table is dependent upon the Author table.

We then inserted some dummy records into both tables.

Now let’s create two stored procedures: one dependent on the Book table and the other dependent on both the Book and Author tables.

Execute the following script:

In addition, let’s create a view that shows all the records from the Book table.

Finally, we will create a table-valued function that returns book name and author name joined together using INNER JOIN.

Execute the following script:

The summary of objects in the Library database, along with their type and dependencies is as follows:

Object Name Object Type Dependencies
Author Table Nil
Book Table Author Table
sp_GetBooks Stored Procedure Book Table
sp_GetBooksAndAuthors Stored Procedure Book and Author Table
vwAuthors View Author Table
fnGetBooksAndAuthors Table Valued Function Books and Author Table

We can see these dependencies because we created the database and the objects within the database. If the database is designed by someone else, or there are huge number of objects in a database, it is not so easy to keep track of all the dependencies. This is where SQL Server Management Studio can help you to identify object dependencies.

Using SQL Server Management Studio for Identifying Object Dependencies

Let’s see how SQL Server management studio can be used to identify object identifies.

Go to SQL Server Object Explorer -> Databases -> Library -> Tables. Right click on dbo.Author table and select Delete. This is shown in the figure below:

A “Delete Object” dialogue box will appear. Click on the OK button. An error will occur. Click the information in the Message Column to see the error in detail.

The detailed error will look like this:

The error message clearly explains the reason for the error. It says that the dbo.Author object could not be dropped because it is referenced by a FOREIGN KEY constraint. Hence it cannot be deleted.

In this case, when we tried to delete a table that the other tables depend upon, SQL Server gives an error. However, this is not the case with all the dependencies.

For instance, although both the stored procedures sp_GetBooks and sp_GetBooksAndAuthors are depending upon the Book table, we can delete the Book table without an error. This is because no other table is depending upon the Book table.

Try to delete the Book table by going to SQL Server Object Explorer -> Databases -> Library -> Tables. You will see that the table will be deleted successfully.

Now try to execute the sp_GetBooks and sp_GetBooksAndAuthors stored procedures that are dependent on the Book table. Execute the following script:

SQL Server management studio throws an error which looks like this:

Since the Book object has been deleted, if we try to execute the stored procedure dependent on Book object, an error is thrown which says that “Invalid object name ‘Book’”.

A similar error will be thrown if you try to execute the sp_GetBooksAndAuthors stored procedure. This shows how important it is to first identify object dependencies before deleting or modifying an object. Otherwise, you risk breaking the script.

Let’s recreate the Book table and insert some dummy records into it.

It is very easy to verify object dependencies in SQL Server. For instance, if you want to check what objects depend upon the Book table, go to SQL Server Object Explorer -> Databases -> Library -> Tables. Right click on “dbo.Book” and select “View Dependencies” from the context menu as shown in the following figure:

Object Dependencies window will appear which looks like this:

By default, all the objects that depend upon the Book object are displayed. You can see fnGetBooksAndAuthors, sp_GetBooks and sp_GetBooksAndAuthors under the object dependencies. You can also check the object on which the Book object depends by selecting the second radio button with the title “Objects on which [Book] depends” as shown in the following screenshot.

You can see “Author” object in the list since this is the only object on which the Book table depends.

You can identify object dependencies for stored procedures in the same way. So if you want to find the objects that the sp_GetBooksAndAuthors stored procedure depends upon, you can simply go to SQL Server Object Explorer -> Databases -> Library -> Programmability -> Stored Procedures and right click the dbo.sp_GetBooksAndAuthors stored procedure. From the context menu, select View Dependency and then select Objects on which [sp_GetBooksAndAuthors] depends upon. This is shown in the following figure:

You can see Book and Author table in the object dependencies window. These are the two objects that the sp_GetBooksAndAuthors stored procedure depends upon.

References:


See more

To track SQL object dependencies down to the column level, check out ApexSQL Analyze


Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
610 Views