SQL Server has a system catalog view called sys.sql_dependencies that returns information about dependencies between entities.
Microsoft has marked this view as deprecated, which means that it’s in maintenance mode and may be removed in a future version of SQL Server. You should avoid using sys.sql_dependencies in new development work, and you should modify applications that currently use it to use sys.sql_expression_dependencies instead.
The sys.sql_expression_dependencies system catalog view returns all dependencies on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules.
You can use this view to:
- Return entities that depend on a given entity
- Return entities on which a given entity depends
For example, you could use it to return all objects that reference a specific table. You could also use it to return all objects that a specific stored procedure references within its code.
For more information and examples, see my post: Find Dependencies in SQL Server: sql_expression_dependencies.
Not Sure if You’re Currently Using sys.sql_dependencies?
If you’re not sure whether your system uses sys.sql_dependencies, you could always use sys.dm_os_performance_counters to find out. You can use this system dynamic management view to return a count of how many times each deprecated feature has been encountered since SQL Server was started. Check out Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance for more info and examples.
A more elaborate method is to use extended events to create a log file that contains extra information about each usage of the deprecated feature. For example, you can record info such as the SQL statement that contains the deprecated feature, the user who ran it, the time it was run, the database it was in, and more. See Using Extended Events to Log Deprecated Features Being Used in a SQL Server Instance for step by step instructions on how to do that.