A very common question which I often receive are:

How do I find all the tables used in a particular stored procedure?

How do I know which stored procedures are using a particular table?

Both are valid question but before we see the answer of this question – let us understand two small concepts – Referenced and Referencing.

Here is the sample stored procedure.

CREATE PROCEDURE mySP
AS
SELECT *
FROM Sales.Customer
GO

Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.

Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.

Find Referecing Objects of a particular object

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).

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
--,sed.* -- Uncomment for all the columns
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'

The above query will return all the objects which are referencing the table Customer.

Find Referenced Objects of a particular object

Here we are finding all the objects which are used in the view table vIndividualCustomer.

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
--,sed.* -- Uncomment for all the columns
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'

The above query will return all the objects which are referencing the table Customer.

I am just glad to write above query. There are more to write to this subject. In future blog post I will write more in depth about other DMV which also aids in finding referenced data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology