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
