Finding Where an Object Is Used Across a Database

To find object usage before a change, combine catalog dependencies with source searches and runtime evidence. No single query can prove that every application, report, and scheduled task has been accounted for.

Confirm the Exact Object

Start with the database and schema-qualified name. Two schemas can contain objects with the same short name. A search that ignores that distinction can mix unrelated callers or miss the intended target.

DECLARE @Target nvarchar(517) = N'dbo.YourTable';
SELECT DB_NAME() AS database_name,
       OBJECT_ID(@Target) AS target_object_id;
SELECT SCHEMA_NAME(schema_id) AS schema_name, name, type_desc
FROM sys.objects
WHERE object_id = OBJECT_ID(@Target);

Replace the example name before using the remaining queries. A NULL object identifier can reflect a wrong name or restricted metadata visibility. Resolve that uncertainty before interpreting an empty dependency result.

Ask the Dependency Function

The referencing-entities function reports persisted references in the current database. It is a useful first map for procedures, views, and other supported objects. It does not search every database on the instance.

SELECT referencing_schema_name, referencing_entity_name,
       referencing_id, referencing_class_desc,
       is_caller_dependent
FROM sys.dm_sql_referencing_entities(N'dbo.YourTable', N'OBJECT');

Partial metadata permissions can produce partial results. Caller-dependent references may require runtime context to resolve. Keep those qualifications with the report instead of presenting the result as a complete dependency graph.

The reverse function, sys.dm_sql_referenced_entities, answers what a chosen module references. Use it when following a chain outward from a procedure. The two directions are easy to confuse during a hurried review.

Inspect Named References Across Boundaries

The dependency catalog can preserve names for cross-database or cross-server references even when object identifiers cannot be resolved locally. Search both identifiers and names where appropriate. Review the database and server qualifiers before accepting a match.

DECLARE @TargetId int = OBJECT_ID(N'dbo.YourTable');
SELECT OBJECT_SCHEMA_NAME(referencing_id) AS caller_schema,
       OBJECT_NAME(referencing_id) AS caller_name,
       referenced_server_name, referenced_database_name,
       referenced_schema_name, referenced_entity_name,
       referenced_id, is_caller_dependent
FROM sys.sql_expression_dependencies
WHERE referenced_id = @TargetId
   OR referenced_entity_name = N'YourTable';

This broad name condition deliberately returns candidates that need inspection. It can include another object with the same short name. For incoming references from other databases, run the relevant search in each approved database context.

Search Definitions and Synonyms

Text search can reveal names embedded in dynamic SQL literals that ordinary dependency tracking misses. It can also match comments, old code, and longer unrelated identifiers. Read each hit rather than treating it as an active call.

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id) AS module_name,
       o.type_desc
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE CHARINDEX(N'YourTable', m.definition) > 0;
SELECT SCHEMA_NAME(schema_id) AS schema_name,
       name, base_object_name
FROM sys.synonyms
WHERE CHARINDEX(N'YourTable', base_object_name) > 0;

A dynamically assembled identifier may never appear as one complete string. Search the construction logic when fragments or configuration tables provide names. Encrypted modules require an approved source copy or another evidence path.

Synonyms add another name that callers may use. Follow the base object and search for the synonym as well. Remember that synonym bindings are resolved by name and can change independently of the original caller.

Look Outside the Database

Applications, reports, data-load packages, and Agent steps can contain SQL that the database catalog never stores as a module. Ask the owners and search the maintained source. An ad hoc analyst query may exist only in a local file.

SELECT j.name AS job_name, s.step_id, s.step_name,
       s.subsystem, s.database_name
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE CHARINDEX(N'YourTable', s.command) > 0;

This job search requires appropriate msdb visibility and only finds literal text matches. It does not inspect external scripts launched by a step. Follow those file references through the approved operational inventory.

Use Runtime Evidence Without Overclaiming

Query Store or a focused Extended Events capture can show observed statements that use the object. Collection scope, retention, and text construction limit what you can find. A monthly task may not appear during a short observation period.

Combine confirmed callers, possible callers, and unresolved visibility gaps in the change record. Test the intended application paths before altering or removing the object. No observed use is weaker evidence than a complete agreement that the interface is retired.

Repeat the search after the migration and keep a meaningful observation window. The goal is to reduce uncertainty enough for the planned change. A clean query result by itself is not that decision.

Dependency discovery is not one perfect query, it is several imperfect views checked together.

This post was rewritten from scratch in September 2026. The original, published on 2016-02-02, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.