Sql-server – Finding all functions/stored procedures that reference a certain procedure/function

sql serversql-server-2012

I have been given the task to move reports from SSRS to our internal ASP.Net application and this has involved me re-writing a few functions and a stored procedure to both improve performance, make them output in forms better consumed by the application and make any requested additions to the reports.

What I also need to do is remove the old functions and procedures from the system as I go, before I do this though I want to make sure it will not break anything else. Is there a way I can go though all the precedures and functions to see if any of them contain references to the ones I will be removing?

There are thousands of these to go though and I would rather not have to open each one at a time in SSMS to look. So if there is some table I can query or even if there is a .sql file that SQL server is hiding that I could dig though, that would be great.

Best Answer

sys.sql_expression_dependencies should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).

I think you can get what you want with the following query:

    o.name AS ReferencingObject, 
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';