Object Dependencies in SQL Server
In SQL Server, there are objects like Tables, Views, Stored Procedures etc. Each one of them may have dependencies between each other. A table may depend upon other table(s) or View may depend on a single table or many tables, for example.
SQL Programmers actually create these Objects & their dependencies every time at all places. Sometimes we may want to know or view these dependencies.
So, we will check two methods from which one can view the dependencies between created Objects in SQL server.
Northwind database is being used here.
Method 1 to find Object Dependencies
With this method one can use SQL Server’s wizard option.
Select a Table -> Right click on it -> Select View Dependencies option.
It will show the dependencies on which Object depends & also those objects which are depending on it (current object).
After selecting View Dependencies option, an Object Dependencies dialog will open & shows the Objects that are depending on selected entity (Employee table as in our case).
Similarly you can select second radio button "Objects on which [Employees] depends" in order to view objects on which Employees depends.
Method 2 to find Object Dependencies
Programmer can also view these dependencies between objects with the help of SQL Queries.
sp_MSdependencies is a stored procedure that helps us to find the dependencies of any given object.
Syntax:
EXEC sp_MSobject_dependencies param_name, param_type
Param_name: It refers to the Object’s name for which we want to view dependencies.
Param_type: It specifies whether we want to see Objects which are depending on our Object or Objects on which our Object depends.
Points to remember:
Value 131527 shows objects which are dependent on the specified object.
Value 1053183 shows objects on which the specified object is dependent.
Example:
1) With table
a) For viewing Objects that are depending on selected Object (Employee table in our case), we can write query as below:
EXEC sp_MSdependencies N'dbo.[Employees]', null, 1315327;
Output (Following objects depend on Employees):
It has shown us all the dependencies. Those dependencies which have 8 as oType’s value, those are Tables, 4 are for Views & 16 for Stored Procedures.
b) For viewing Objects that the specified object is dependent on (Orders Table in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Orders]', null, 1053183;
Output (Orders is dependent on following objects):
2) With View:
a) For viewing Objects that the specified object is dependent on (Invoices view in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Invoices]', null, 1053183;
Output (View Invoices is dependent on following objects):
b) For viewing Objects that are depending on selected Object (Invoices View in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Invoices]', null, 1315327;
Output (Following objects depend on View Invoices):
In SQL Server, there are objects like Tables, Views, Stored Procedures etc. Each one of them may have dependencies between each other. A table may depend upon other table(s) or View may depend on a single table or many tables, for example.
SQL Programmers actually create these Objects & their dependencies every time at all places. Sometimes we may want to know or view these dependencies.
So, we will check two methods from which one can view the dependencies between created Objects in SQL server.
Northwind database is being used here.
Method 1 to find Object Dependencies
With this method one can use SQL Server’s wizard option.
Select a Table -> Right click on it -> Select View Dependencies option.
It will show the dependencies on which Object depends & also those objects which are depending on it (current object).
After selecting View Dependencies option, an Object Dependencies dialog will open & shows the Objects that are depending on selected entity (Employee table as in our case).
Similarly you can select second radio button "Objects on which [Employees] depends" in order to view objects on which Employees depends.
Method 2 to find Object Dependencies
Programmer can also view these dependencies between objects with the help of SQL Queries.
sp_MSdependencies is a stored procedure that helps us to find the dependencies of any given object.
Syntax:
EXEC sp_MSobject_dependencies param_name, param_type
Param_name: It refers to the Object’s name for which we want to view dependencies.
Param_type: It specifies whether we want to see Objects which are depending on our Object or Objects on which our Object depends.
Points to remember:
Value 131527 shows objects which are dependent on the specified object.
Value 1053183 shows objects on which the specified object is dependent.
Example:
1) With table
a) For viewing Objects that are depending on selected Object (Employee table in our case), we can write query as below:
EXEC sp_MSdependencies N'dbo.[Employees]', null, 1315327;
Output (Following objects depend on Employees):
It has shown us all the dependencies. Those dependencies which have 8 as oType’s value, those are Tables, 4 are for Views & 16 for Stored Procedures.
b) For viewing Objects that the specified object is dependent on (Orders Table in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Orders]', null, 1053183;
Output (Orders is dependent on following objects):
2) With View:
a) For viewing Objects that the specified object is dependent on (Invoices view in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Invoices]', null, 1053183;
Output (View Invoices is dependent on following objects):
b) For viewing Objects that are depending on selected Object (Invoices View in our case), we can write a query as below:
EXEC sp_MSdependencies N'dbo.[Invoices]', null, 1315327;
Output (Following objects depend on View Invoices):