How to find SQL server Object dependency

How to find SQL server Object dependencies

In T-SQL, we can retrieve all dependent objects across all databases, including those from remote server linked objects. The `sql_expression_dependencies` system view contains one row for each dependency on a user-defined entity in the current database. This includes dependencies between scalar, user-defined functions and other SQL Server modules, including natively compiled ones.

CREATE TABLE #alldep (
  dbname VARCHAR(100),
  objname VARCHAR(100),
  sdbname VARCHAR(100),
  sschma VARCHAR(100),
  stable VARCHAR(100),
  sserver VARCHAR(100)
)

INSERT INTO #alldep
EXEC SP_MSFOREACHDB 'USE ? 
  SELECT db_name(), 
         OBJECT_NAME(referencing_id) AS referencing_object,
         referenced_database_name,
         referenced_schema_name,
         referenced_entity_name,
         referenced_server_name
  FROM sys.sql_expression_dependencies
  WHERE referenced_database_name IS NOT NULL
  AND is_ambiguous = 0'

SELECT * FROM #alldep
SELECT * FROM #alldep WHERE sserver <> ''

For more information on the system view `sys.sql_expression_dependencies`, check the official documentation here: Microsoft Docs