Have you ever wondered how to find all the tables used in a particular stored procedure? Or maybe you need to know which stored procedures are using a specific table? In this blog post, we will explore the sys.sql_expression_dependencies feature in SQL Server that can help you answer these questions.

Before we dive into the details, let’s first understand two important concepts – Referenced and Referencing objects. In a stored procedure, the referenced object is the table that is being used, while the referencing object is the stored procedure itself that references the table.

Let’s take a look at a sample stored procedure:

CREATE PROCEDURE mySP
AS
SELECT *
FROM Sales.Customer
GO

In this example, the table “Sales.Customer” is the referenced object, as it is being used in the stored procedure “mySP”. On the other hand, the stored procedure “mySP” is the referencing object, as it references the “Sales.Customer” table.

Now, let’s run some queries to find the referencing and referenced objects using the AdventureWorks2012 sample database.

Finding Referencing Objects

To find all the objects that are referencing a specific table (regardless of the schema), you can use the following query:

USE AdventureWorks
GO
SELECT
    referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
    referencing_object_name = o.name,
    referencing_object_type_desc = o.type_desc,
    referenced_schema_name,
    referenced_object_name = referenced_entity_name,
    referenced_object_type_desc = o1.type_desc,
    referenced_server_name,
    referenced_database_name
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
    referenced_entity_name = 'Customer'

This query will return all the objects that are referencing the table “Customer”. You can modify the “referenced_entity_name” parameter to search for different tables.

Finding Referenced Objects

If you need to find all the objects that are used in a specific view or table, you can use the following query:

USE AdventureWorks
GO
SELECT
    referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
    referencing_object_name = o.name,
    referencing_object_type_desc = o.type_desc,
    referenced_schema_name,
    referenced_object_name = referenced_entity_name,
    referenced_object_type_desc = o1.type_desc,
    referenced_server_name,
    referenced_database_name
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 = 'vIndividualCustomer'

This query will return all the objects that are referenced by the view or table “vIndividualCustomer”. You can modify the “o.name” parameter to search for different views or tables.

Understanding SQL Server dependencies is crucial for managing and maintaining your database. The sys.sql_expression_dependencies feature provides valuable insights into the relationships between objects, allowing you to make informed decisions when making changes to your database schema.

In future blog posts, we will explore more in-depth about other DMVs that can help you find referenced data. Stay tuned!