Finding Procedures That Reference Databases That No Longer Exist

Retiring a database does not remove every reference to its name. Procedures can still reference databases that no longer exist and fail only when an overlooked path executes. Dependency metadata gives you a useful first inventory.

Define Which Procedures Reference Databases Locally

A three-part object name can reference another database on the same instance. A four-part name can reference a database on another server. Comparing that remote name with local sys.databases would create a false missing-database warning. Keep those cases separate.

I identify the server scope before calling a reference broken. The database can be absent locally and perfectly valid on its referenced server. Metadata visibility matters too. A login that cannot see every database name can turn an incomplete inventory into an alarming list of apparent losses.

The following query limits itself to local cross-database references from procedures. It reports the referencing schema and object beside the referenced names. A missing match is a review candidate, not an instruction to edit the procedure automatically. COLLATE DATABASE_DEFAULT avoids a collation conflict in a database whose collation differs from the server. The is_ambiguous filter skips method calls, such as an xml value() call, which the catalog stores with an alias in the database position.

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ProcedureSchema,
       OBJECT_NAME(d.referencing_id) AS ProcedureName,
       d.referenced_database_name,d.referenced_schema_name,
       d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.procedures p ON p.object_id=d.referencing_id
LEFT JOIN sys.databases db
 ON db.name=d.referenced_database_name COLLATE DATABASE_DEFAULT
WHERE d.referenced_database_name IS NOT NULL
 AND d.is_ambiguous=0
 AND d.referenced_server_name IS NULL
 AND db.database_id IS NULL
ORDER BY ProcedureSchema,ProcedureName;

Preserve Existing References Too

A review also benefits from the complete cross-database inventory. A database that exists can still be offline, inaccessible, or missing the referenced object. Existence is only one part of resolution. Keep its state and access context beside the reference.

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ProcedureSchema,
       OBJECT_NAME(d.referencing_id) AS ProcedureName,
       d.referenced_server_name,d.referenced_database_name,
       db.state_desc,db.user_access_desc
FROM sys.sql_expression_dependencies d
JOIN sys.procedures p ON p.object_id=d.referencing_id
LEFT JOIN sys.databases db
 ON db.name=d.referenced_database_name COLLATE DATABASE_DEFAULT
 AND d.referenced_server_name IS NULL
WHERE d.referenced_database_name IS NOT NULL
 AND d.is_ambiguous=0;

An online database can still reject the caller because of permissions or execution context. Do not change security settings to make an inventory warning disappear. Test the intended call under the application identity in a controlled environment. That reveals the behavior the real caller experiences.

Review the Module and Its Owner

Read the procedure definition before choosing a correction. The reference can be in an obsolete branch, a periodic operation, or a still-required dependency. Search the authorized deployment source as well, because a later deployment can restore an old name if only the current database module changes.

Do not replace names globally based on spelling resemblance. The retired database and its apparent replacement can have different schemas or business meaning. Confirm the replacement object, result contract, and security behavior with the owner.

I retain the original definition with each proposed correction. Which caller still needs this procedure, and what data source should its operation use now? Those questions make the change review concrete. A missing database name is a symptom; the application's intended behavior supplies the answer.

Repeat the Reference Check Across All Databases

Dependency metadata belongs to each database. Enumerate visible online user databases and execute the same check within each context. QUOTENAME protects database identifiers. Capture successful checks and errors separately so zero findings cannot be confused with an unvisited database.

CREATE TABLE #MissingDatabaseRefs
(SourceDatabase sysname,ProcedureSchema sysname,ProcedureName sysname,
 ReferencedDatabase sysname,ReferencedSchema sysname,ReferencedObject sysname);
CREATE TABLE #DependencyCoverage
(DatabaseName sysname,ReviewError nvarchar(2048) NULL);
DECLARE @db sysname,@sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
 SELECT name FROM sys.databases WHERE database_id>4 AND state=0;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS=0
BEGIN
 BEGIN TRY
 SET @sql=N'USE '+QUOTENAME(@db)+N';
 INSERT #MissingDatabaseRefs
 SELECT DB_NAME(),OBJECT_SCHEMA_NAME(d.referencing_id),
 OBJECT_NAME(d.referencing_id),d.referenced_database_name,
 d.referenced_schema_name,d.referenced_entity_name
 FROM sys.sql_expression_dependencies d
 JOIN sys.procedures p ON p.object_id=d.referencing_id
 LEFT JOIN sys.databases b
 ON b.name=d.referenced_database_name COLLATE DATABASE_DEFAULT
 WHERE d.referenced_database_name IS NOT NULL AND d.is_ambiguous=0
 AND d.referenced_server_name IS NULL AND b.database_id IS NULL;
 INSERT #DependencyCoverage VALUES(DB_NAME(),NULL);';
 EXEC sys.sp_executesql @sql;
 END TRY
 BEGIN CATCH
 INSERT #DependencyCoverage VALUES(@db,ERROR_MESSAGE());
 END CATCH;
 FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM #DependencyCoverage ORDER BY DatabaseName;
SELECT * FROM #MissingDatabaseRefs ORDER BY SourceDatabase,ProcedureName;

The script is an inventory, not a repair batch. Offline databases and hidden database metadata remain outside its scope. Record those omissions from an administrator's full instance inventory. A state change during execution can also produce a captured error rather than a finding.

Check Dynamic SQL That Can Reference Databases

Names constructed at runtime are not fully represented by expression dependency tracking. A procedure can concatenate a database name or receive it as an input. A dependency query cannot infer every possible resulting string.

Search module text for a known retired database name as an additional candidate pass. Text matching can find comments and unrelated strings, so it still needs review. Encrypted modules do not expose their definitions here. Locate their authorized source instead of treating NULL as no dependency.

DECLARE @RetiredName nvarchar(128)=N'RetiredReportingDatabase';
SELECT OBJECT_SCHEMA_NAME(object_id) AS ObjectSchema,
       OBJECT_NAME(object_id) AS ObjectName,definition
FROM sys.sql_modules
WHERE CHARINDEX(@RetiredName,definition)>0;

Use a literal search expression carefully when the name contains special characters. CHARINDEX avoids LIKE wildcard interpretation for this simple pass. It still cannot discover a name assembled from several unrelated fragments or stored in application configuration.

Inventory Synonym Targets

Synonyms hold another level of indirection. Their base_object_name can contain a local or remote target. Inspect them separately and distinguish the server component before comparing the database component with local inventory.

SELECT SCHEMA_NAME(schema_id) AS SynonymSchema,name AS SynonymName,
       base_object_name,PARSENAME(base_object_name,4) AS TargetServer,
       PARSENAME(base_object_name,3) AS TargetDatabase
FROM sys.synonyms
ORDER BY SynonymSchema,SynonymName;

PARSENAME is a convenient multipart-name aid, not a full resolver for every unusual quoted identifier. Review the stored target directly when its parts are unclear. A synonym with no explicit database component resolves within its relevant local context, so a NULL parsed database is not automatically missing.

Test the Proposed Correction

Use a controlled database copy to change and execute the affected procedure paths. Compare results with the approved requirement, including empty inputs and scheduled operations. Verify permissions under the actual caller rather than only an administrator.

Do not run all discovered procedures automatically. Some perform writes, send notifications, or invoke operational work. Choose tests from the module's behavior and the owner's approval. The inventory is a map, not a button marked run every road.

Retain the Unresolved Work

Save the source database, module identity, referenced name, capture time, and reviewing owner. Record confirmed broken references separately from unresolved candidates and deliberate remote references. Keep the coverage errors visible until they have been addressed.

After approved corrections, repeat both metadata and targeted text checks. Verify scheduled callers through their normal controlled test process. That closes the evidence loop without claiming that one catalog view can discover every dynamically assembled name in the environment.

An approved retirement record can also help classify findings. It should name the original database, replacement data source, retirement date, and retained recovery evidence. Compare the procedure's purpose with that record. A name that disappeared during a restore or rename needs a different investigation from a deliberately retired application. Keep those situations distinct in the report. If the retirement decision has no replacement for the procedure's operation, the owner must decide whether to remove that path or redesign it. The catalog cannot supply that business decision simply because its join returned NULL.

Procedures that reference databases need a complete source-context inventory. Review remote and dynamic names separately before correcting procedures that reference databases absent from the local instance.

Related reading on this blog: Last Used Stored Procedure and Linked Servers and What Goes Wrong With Them.

A missing dependency match is not a complete repair instruction, it is evidence that needs scope, visibility, and application intent checks.

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