How To: Find all objects, referencing a single object or all objects, referenced in a single object in SQL Server Database

Many times i come across a situation where i have a requirements like this.

1.   Find out all stored procedures, using a table or

2.   Find out all tables used in a stored procedure.

Today I will show how to get this information using sys.sql_expression_dependencies system object.

Before we go further, we need to know about the concept of Referenced and Referencing object. Here i am explaining this concept by an example.

This is the definition of a stored procedure named Up_Sample.

Create Procedure Up_Sample

AS

Select * from Tbl_Example

GO

Referencing Object: Here Up_Sample stored procedure is using Tbl_Example table, hence Up_Sample is referencing object.

Referenced Object: Here Tbl_Example table is being used by Up_Sample stored procedure, hence Tbl_Example referenced object.

Now that you know the concept of Referenced and Referencing object, we can go further.

I am using AdventureWorks2008 database in my example. If you don’t have AdventureWorks2008 database in your SQL Server instance then please read my post to Download and install AdventureWorks2008 database and install database accordingly.

1.      Find all objects, referencing a single object

Run the below query to get All objects using or referencing table JobCandidate.

USE AdventureWorks2008;

GO

SELECT

SCHEMA_NAME(O.SCHEMA_ID) as referencing_schema_name,

O.name as referencing_object_name,

O.type_desc as referencing_object_type_desc,

SED.referenced_schema_name,

SED.referenced_entity_name as referenced_object_name,

O1.type_desc as referenced_object_type_desc

FROM

sys.sql_expression_dependencies SED

INNER JOIN

sys.objects O ON SED.referencing_id = O.[object_id]

LEFT OUTER JOIN

sys.objects O1 ON SED.referenced_id = O1.[object_id]

WHERE SED.referenced_entity_name = ‘JobCandidate’

 

Here is the output of this query. You can see all objects using JobCandidate table in this output.

 

clip_image002

Fig. 1

2.     Find all objects, Referenced in a single object

Run the below query to get all objects referenced or used in view vEmployee.

USE AdventureWorks2008

GO

SELECT

SCHEMA_NAME(O.SCHEMA_ID) as referencing_schema_name,

O.name as referencing_object_name,

O.type_desc as referencing_object_type_desc,

SED.referenced_schema_name,

SED.referenced_entity_name as referenced_object_name,

O1.type_desc as referenced_object_type_desc

FROM

sys.sql_expression_dependencies SED

INNER JOIN

sys.objects O ON SED.referencing_id = O.[object_id]

LEFT OUTER JOIN

sys.objects O1 ON SED.referenced_id = O1.[object_id]

WHERE

O.name = ‘vEmployee’

 

Here is the output of this query. You can see all objects referenced in vEmployee view in this output

 

clip_image004

Fig. 2

If you have any question or if you want to share your thoughts on this topic then please feel free to write in the comment section.

Advertisements

About Ashish Jain

I am Ashish Jain, a software engineer by profession. My goal of creating this blog is to share my knowledge of SQL server with all other SQL enthusiasts and also to learn from them.
This entry was posted in SQL Administration and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s