Query dependencies of an object

Observed sign or problem

Before modifying a table, view or procedure we want to know what objects it mentions and what other objects mention it. The question is not just “does it exist?”, but “what could be affected?”

What we need to distinguish

A dependency has an address. The object containing the expression is the referent; the object named by that expression is the referenced one. We will review both addresses separately.

Query

DECLARE @Objeto NVARCHAR(517) = N'dbo.usp_ConsultarPedidos';
DECLARE @IdObjeto INT = OBJECT_ID(@Objeto);

IF @IdObjeto IS NULL
    THROW 50000, 'El objeto no existe o no es visible en esta base.', 1;

-- Objetos nombrados por el objeto investigado.
SELECT
    d.referenced_server_name AS Servidor,
    d.referenced_database_name AS BaseDatos,
    d.referenced_schema_name AS Esquema,
    d.referenced_entity_name AS ObjetoReferenciado,
    COL_NAME(d.referenced_id, d.referenced_minor_id) AS ColumnaReferenciada,
    d.is_ambiguous AS ReferenciaAmbigua
FROM sys.sql_expression_dependencies AS d
WHERE d.referencing_id = @IdObjeto
ORDER BY
    d.referenced_database_name,
    d.referenced_schema_name,
    d.referenced_entity_name;

-- Objetos de la base actual que nombran al objeto investigado.
SELECT
    s.name AS Esquema,
    o.name AS ObjetoReferenciante,
    o.type_desc AS Tipo
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.objects AS o
    ON o.object_id = d.referencing_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
WHERE d.referenced_id = @IdObjeto
ORDER BY
    o.type_desc,
    s.name,
    o.name;

Parameters or permissions

A persisted expression is a definition stored in the catalog. referenced_id is the internal identifier of the referenced object when SQL Server can resolve it locally.

Use a two-part name in @Objeto, such as dbo.Pedido or dbo.usp_ConsultarPedidos. OBJECT_ID resolves to the current base and may return NULL if the object does not exist or is not visible.

The view records dependencies by name present in persisted SQL expressions. For references between databases or servers, names may be available even if referenced_id cannot be resolved to a local identifier.

How to read the result

An ambiguous reference is one for which SQL Server cannot determine a single valid object.

The first result answers “what does this object name?” A database, schema, or server in NULL usually indicates that that part was not explicitly written or that it is a local reference.

The second answers “what local resolved objects name this object?” A view that appears here deserves review before changing a column it uses.

ReferenciaAmbigua = 1 warns that SQL Server cannot unambiguously resolve the reference at dependency storage time. It is not advisable to treat it as a confirmed relationship without reading the expression.

What this does establish

Allows you to document dependencies by name that SQL Server has registered and that are visible to the account. It is useful evidence to estimate the initial scope of a change.

What this still does not establish

A job is a task scheduled through SQL Server Agent. It may also run code that these dependencies do not represent.

It is not a complete execution map. Dynamic SQL, references built at runtime, application code, jobs, and other external components may be left out. It also doesn’t show that a route runs in production or how often.

Just because an object does not appear as a dependent does not automatically make a change safe. Modules with deferred or unresolved references require additional review.

Next reasonable check

Compare the result with textual search within SQL objects. If you investigate tables, check the declared relationships and their key and index structure. For a real change, add in-app call monitoring and testing.

Search SQL Server