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.

CREATE TABLE dbo.Employee
 (Id int IDENTITY(1,1), FirstName NVarchar(50), LastName NVarchar(50))
GO
 INSERT INTO dbo.Employee(FirstName, LastName)
 VALUES('Aesha','Kansagra')
GO
CREATE PROCEDURE dbo.GetEmployeeList_1
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

CREATE PROCEDURE dbo.GetEmployeeList_2
 AS
 BEGIN
  exec dbo.GetEmployeeList_1 –--Call Store procedure 
 END
GO

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

sp_depends 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:

CREATE PROCEDURE dbo.GetEmployeeList_2
 AS
 BEGIN
  exec dbo.GetEmployeeList_1 --Call Store procedure 
 END
GO

CREATE PROCEDURE dbo.GetEmployeeList_1
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

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

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.GetEmployeeList_1', 'OBJECT')

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.

Method 3:
–Find Only List of Table dependency

SELECT referenced_schema_name, referenced_entity_name,referenced_minor_name,
referenced_id, referenced_class_desc, is_caller_dependent
FROM sys.dm_sql_referenced_entities ('dbo.GetEmployeeList_1', 'OBJECT')

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:

Thanks and don’t forget to share your views 🙂