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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
CREATE DATABASE Library GO USE Library; CREATE TABLE Author ( id INT PRIMARY KEY IDENTITY, author_name VARCHAR(50) NOT NULL, ) GO CREATE TABLE Book ( id INT PRIMARY KEY IDENTITY, book_name VARCHAR(50) NOT NULL, price INT NOT NULL, author_id INT FOREIGN KEY REFERENCES Author(id) ) GO USE Library; INSERT INTO Author VALUES ('Author1'), ('Author2'), ('Author3'), ('Author4'), ('Author5'), ('Author6'), ('Author7') INSERT INTO Book VALUES ('Book1',500, 1), ('Book2', 300 ,2), ('Book3',700, 1), ('Book4',400, 3), ('Book5',650, 5), ('Book6',400, 3) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
GO -- Create procedure that depends on Book table CREATE PROCEDURE sp_GetBooks AS BEGIN SELECT * FROM Book END GO --Create procedure that depends on both book and author table CREATE PROCEDURE sp_GetBooksAndAuthors AS BEGIN SELECT Book.book_name as BookName, Author.author_name as AuthorName FROM Book JOIN Author ON Book.author_id = Author.id END |
In addition, let’s create a view that shows all the records from the Book table.
|
1 2 3 4 5 |
USE Library GO CREATE VIEW vwAuthors AS SELECT * FROM Author |
Finally, we will create a table-valued function that returns book name and author name joined together using INNER JOIN.
Execute the following script:
|
1 2 3 4 5 6 7 8 9 10 |
USE Library GO CREATE FUNCTION fnGetBooksAndAuthors() RETURNS TABLE AS RETURN SELECT Book.book_name as BookName, Author.author_name as AuthorName FROM Book JOIN Author ON Book.author_id = Author.id |
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:
|
1 2 |
USE Library EXEC sp_GetBooks |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE Library; CREATE TABLE Book ( id INT PRIMARY KEY IDENTITY, book_name VARCHAR(50) NOT NULL, price INT NOT NULL, author_id INT FOREIGN KEY REFERENCES Author(id) ) INSERT INTO Book VALUES ('Book1',500, 1), ('Book2', 300 ,2), ('Book3',700, 1), ('Book4',400, 3), ('Book5',650, 5), ('Book6',400, 3) |
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.
Other great articles from Ben
| Sequence Objects in SQL Server |
| Difference between Identity & Sequence in SQL Server |
| Identifying Object Dependencies in SQL Server Management Studio |
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021