Get object dependencies for SQL Server in TSQL

JJ Prinsloo Object dependencies Featured Image

We all know there are many features within management studio that we take for granted. One of these features is the Object Dependencies feature. In short, this tool will show you any objects which are dependent a chosen object. For instance, if you want to check which objects are dependent on a specific table. Object dependencies will include any Views, Stored Procedures etc. which references the table in question.

This information is easily retrievable in management studio by right-clicking on the object you want to check and clicking on “View Dependencies”. The following screen will show with a list of dependencies for the chosen object.

1
2
Object Dependencies Screen - JJ Prinsloo
1

Source Object

2

Referencing Objects

Recently, I had to develop a process to automatically deploy SQL scripts to a specific server through a TFS process. Although I did not create the TFS process, I had to make sure that TFS could execute one stored procedure to figure out which objects to create or alter and in which order.

As an example, the problem is that if you try to create a View which references a table which does not exist, the creation of the view will fail which means that the process will fail.

All I had to work with was the object names, the script files and the database. Hmmm. That View Dependencies functionality would come in pretty handy right now. The problem is that I need the results in SQL so that I can query the results to check which objects have dependencies and in which order I should execute my deployment SQL scripts to ensure a successful deployment.

I set out to write my own stored procedure to analyze object dependencies for me.

To install the object dependencies procedure, run the install script on the database on which you want to analyze the object dependencies.

To use the object dependencies stored procedure execute the following command:

exec sp_GetObjectDependencies

The procedure will execute with the default parameter which analyzes all objects on the database excluding constraints.

The main 2 columns to note in the result set is the SourceObject and the ReferencingObject. The SourceObject is the object which was analyzed. The ReferencingObject is the object which is dependent on the source object.

To view all options available, execute the object dependencies stored procedure with the @help parameter:

exec sp_GetObjectDependencies @Help = 0

Now you can view all object dependencies on an object in SQL Server in data format which can be queried and used to achieve all sorts of wonderful things.

Object dependencies Install Script:

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *