Let us learn in this blog about sys.sql_expression_dependencies.
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 posts I will write more in depth about other DMV, which also aids in finding referenced data.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Nice post Pinal – great little tool!
How about use 「sp_depends」?
EXEC sp_depends @objname = N’Customer’;
sp_depends objname works as well
nice one…
Nice Pinal, but the output of second query is same as first one, which is wrong. I think it is wrongly pasted.
I just came across this – neither the script or sp_depends work when the referenced object does not exist when the referencing object is created. After the referenced object is created the SP would need to be altered or recreated to register the dependency. If you were auditing a server you could miss a lot of dependencies due to this. In addition this would not track 3 part named objects in other db’s even if objects were created in the correct order. I prefer a more comprehensive check using sys.sql_modules, depending on the confidence level of the database sanitisation.
sys.dm_sql_referencing_entities() built-in founction is better
First, thanks for this – very helpful! Almost exactly what I needed…
“Almost”, because I have a few procedures that build a string and then exec it.
(e.g., set @Query = “SELECT … FROM Object_I_Need_To_Find_References…”; exec @Query)
Any tips on finding all of those – short of remembering where I put them (always dangerous)?
Thanks!
Hi All,
i got an issue when i’m performing the snapshot replication for DR. when i tried to perform the snapshot replication i got the error as “Objects referenced by the Stored procedures are required”. I’m new to the SQL administration, can anyone help me on this issue to move forward.
Thanks in advance.
Nice post. Good work.
Thanks, Pinal. Once again, you’re the best!
Muito bom! (Very Good!)
Thanks Pinal and Sorry for the late reply, but have you found a way of getting a column-level dependency?
found below on StackExchange
SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + ‘.’ + o1.name,
ReferencedObject = SCHEMA_NAME(o2.schema_id) + ‘.’
+ ed.referenced_entity_name,
ColumnName = c.name,
ReferencedObjectType = o2.type,
ReferencingObjecType = o1.type
FROM AdventureWorks2012.sys.sql_expression_dependencies ed
INNER JOIN AdventureWorks2012.sys.objects o1
ON ed.referencing_id = o1.object_id
INNER JOIN AdventureWorks2012.sys.objects o2
ON ed.referenced_id = o2.object_id
INNER JOIN AdventureWorks2012.sys.sql_dependencies d
ON ed.referencing_id = d.object_id
AND d.referenced_major_id = ed.referenced_id
INNER JOIN sys.columns c
ON c.object_id = ed.referenced_id
AND d.referenced_minor_id = c.column_id
WHERE SCHEMA_NAME(o1.schema_id) + ‘.’ + o1.name = ‘HumanResources.vEmployee’
ORDER BY ReferencedObject,
c.column_id;
Thanks Pinal, I actually found a similar example however for some reason the sys.sql_dependencies does not show the actual dependencies, looks like some dependencies are completely missing, I will keep trying