Home » Tutorials » SQL SERVER » Get Dependency of a table, SP and view in SQL

Get Dependency of a table, SP and view in SQL

pivot and unpivot in sql server
Synonyms in SQL Server

We have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() , Aggregate functions in sql server and Identity column in sql server. Today I am going to explain regarding Get Dependency of a table, SP and view in SQL.

Using SQL Server Management Studio

Recently one of my friend needs the dependencies of the stored procedures.I researched about it and found some solution which may help you. There is a statement which gives the list of store procedures in output which defines the dependency of another store procedure.

To view the dependencies of a procedure in Object Explorer
1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
3. Expand Stored Procedures, right-click the procedure and then click View Dependencies.
4. View the list of objects that depend on the procedure.
5. View the list of objects on which the procedure depends.
6. Click OK.

Follow above step will definitely give you result, but if you want that result as list and want to copy somewhere then what !! For that you have to write query, which i will going to explain here.

Using Transact-SQL
To view the dependencies of a procedure in list, you can write query in Query Editor.
Let me explain this with an example. First create a table, then a stored procedure which references the table and execute store procedure into another store procedure.

Method 1:
— Get the list of objects referenced by the SP:GetEmployeeList_1

It is clear that sp_depends does not give proper/correct results if you have refrenced one store procedure which is not created before refrencing. Here Clear one thing that you can create procedure if refrencing store procedure is yet to be create. It will not give correct result try yourself, you will get idea.

Following query will not give correct result:

Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeList_1.

Method 2:
–Find Only List of Store Procedure dependency

Using this query you will get dependencies of ‘GetEmployeeList_1’ store procedure. In this query you can replace store procedure name with table name and you will get dependencies for table also. Following will be the output of above query.Try for table depency by yourself.

Get_dependency_1

Method 3:
–Find Only List of Table dependency

Using this query you will get dependencies of ‘GetEmployeeList_1’ store procedure. But you will not get any result if you replace it with table name. So, you can say refrenced_entities will only give result for store procedure. Following will be the output of above query:

Get_dependency_2

Thanks and don’t forget to share your views 🙂

3 thoughts on “Get Dependency of a table, SP and view in SQL

  1. Nilesh Jethava says:

    Good .. Helpful for us to find dependencies of objects

  2. Ami says:

    Its really nice. Thank you to upload it

  3. Haren says:

    helpful to us …

Leave a Reply

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

*
*

Time limit is exhausted. Please reload CAPTCHA.




DISCLAIMER:
The content is copyrighted to technothirsty.com and may not be reproduced on other websites without permission from the owner.You may contact us using the information below.