Database Object Dependencies

Sometimes you want to find the database object dependency within a database or instance, and luckily this is something Microsoft has thought of in the form of a view called sys.sqlexpressiondependencies.

Microsoft also tells us a fair bit about this magnificent view:

“(This view) Contains one row for each by-name dependency on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity.”

I wanted to find all database objects that has a dependency to other databases, to make sure that decommissioning one database did not affect a bunch of views in a database we wanted to keep operational.

The view mentioned is a database wide view, so if you need to collect data from a whole instance there are a few ways to automate this, but I have a suggestion using a separate table, and the undocumented procedure sp_MSforeachdb. I am not going to say much about this procedure, other than this is used on your own risk. Microsoft can change or remove this procedure at any point, so please do not use it as part of an important production job or similar.

First you create the query which provide you with the data you think you need. In my case it looked something like this:

SELECT DB_NAME() AS [DBName],
DB_ID() AS [DBid],
OBJECT_SCHEMA_NAME(referencing_id) AS [SchemaName],
OBJECT_NAME(ssed.referencing_id) AS [ObjectName], 
ssed.referenced_database_name,
ssed.referenced_schema_name,
ssed.referenced_entity_name,
isv.VIEW_DEFINITION
FROM sys.sql_expression_dependencies ssed
LEFT JOIN INFORMATION_SCHEMA.VIEWS isv 
    on isv.TABLE_NAME = OBJECT_NAME(ssed.referencing_id)
WHERE referenced_database_name IS NOT NULL;

This query will provide you with a list like the image below, depending on your dependencies. Note that if you have no depencendies outside the database in question, no rows are returned.

[File: ad9bdbf4-2086-4e91-baf4-a071040c1acb]

The data here is from a Reporting Services databases which refer to the ReportServerTempDB. Probably not surprising, but good to know.

I have also fetched some information from the fantastic INFORMATION_SCHEMA. This time I have only collected the definition of the view, however if you are going to export this list into a spreadsheet to share with someone, you might want to comment this out. In my experience Excel does not handle multi-line columns especially well.

Next part is to make sure you have a table that will take all the data provided by the query created. I created the following table in my DBA_data. We can discuss whether it should have some index or not, but lets look at that another time.

CREATE TABLE dbo.referencesforobjects(
ReferencingDatabaseName nvarchar(256) not null,
ReferencingDatabaseId int,
ReferencingSchemaName nvarchar(256),
ReferencingObjectName nvarchar(256),
referenced_database_name sysname,
referenced_schema_name sysname,
referenced_entity_name sysname,
ViewDefinition nvarchar(max)
);

When the table is created, I am basically ready to run the MS-procedure to execute the select statement (as an insert) to gather the data efficiently.

exec sp_MSforeachdb @command1 = 'USE ?; 
INSERT [DBA_Data].[dbo].[referencesforobjects] 
SELECT DB_NAME() AS [DBName], DB_ID() AS [DatabaseID], 
OBJECT_SCHEMA_NAME(referencing_id) AS [SchemaName], 
OBJECT_NAME (ssed.referencing_id) AS [ObjectName], 
ssed.referenced_database_name, 
ssed.referenced_schema_name, 
ssed.referenced_entity_name, 
isv.VIEW_DEFINITION
FROM sys.sql_expression_dependencies ssed
LEFT JOIN INFORMATION_SCHEMA.VIEWS isv 
on isv.TABLE_NAME = OBJECT_NAME(ssed.referencing_id)
WHERE referenced_database_name IS NOT NULL;'

The query is now executed in every database and the result set is inserted to the dba_data.dbo.referencesforobjects table. You can query it to find the result for a single or multiple database, or even all databases except system dbs.

SELECT [ReferencingDatabaseName]
,[ReferencingSchemaName]
,[ReferencingObjectName]
,[referenced_database_name]
,[referenced_schema_name]
,[referenced_entity_name]
,[ViewDefinition] --Skip column if pasting into Excel
FROM [DBA_Data].[dbo].[referencesforobjects]
WHERE [ReferencingDatabaseId] > 4
ORDER BY [ReferencingDatabaseName];

This is one way to collect information regarding views and other database object that has references to objects in a different database. Good to know how the information in your databases crisscross before the databases are dropped… Also a good way to find views and procedures that no longer works, as they reference databases that do no longer exist.

Lately, I have become aware of a weakness with the referencing table if you use it to find out how to build the access structure when a view references multiple databases. If a view references another view in the same database, but in a different schema, the access structure might not be complete if you only use this view, without any check of the logic afterwards.

Let me know if you have any comments or suggestions, or if there is an easier way to gather this information in large instances.