This is a brief post containing a query that lists object dependencies in SQL Server for either a specific object or for the entire database.
Intro
Figuring out object dependencies in a SQL Server database, especially one you’re not familiar with, can be a challenge.
Luckily, Microsoft provides the sys.sql_expression_dependencies system catalog view to help DBAs and developers track down various object dependencies.
I leveraged this catalog view in a query that returns the necessary info in a more user-friendly way, while also allowing easy filtering for referenced or referencing object.
The query
To search for a specific object, set the value of the @ObjectName parameter to the name of the object.
The object name can be schema-qualified and even wrapped in square brackets.
The query will return all objects that depend on specified object as well as the objects it depends on.
If the @ObjectName parameter is left empty, then the dependencies for the entire database are returned.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | /*provide an object name or leave empty to search the entire database*/ DECLARE @ObjectName NVARCHAR(261) = N''; SELECT QUOTENAME(SCHEMA_NAME([ob].[schema_id])) + N'.' + QUOTENAME([ob].[name]) + ISNULL(N'.'+QUOTENAME([col].[name]), N'') AS [referencing_object_name], [ob].[type_desc] AS [referencing_object_type], ISNULL(QUOTENAME([sed].[referenced_server_name])+N'.', N'') + ISNULL(QUOTENAME([sed].[referenced_database_name])+N'.', N'') + ISNULL(QUOTENAME([sed].[referenced_schema_name])+N'.', N'') + ISNULL(QUOTENAME([sed].[referenced_entity_name]), N'') + ISNULL(N'.' + QUOTENAME([tgcol].[name]), N'') AS [fully_qulified_referenced_object], ISNULL([tgob].[type_desc], [sed].[referenced_class_desc]) AS [referenced_object_type_or_class], [sed].[referenced_server_name], [sed].[referenced_database_name], [sed].[referenced_schema_name], [sed].[referenced_entity_name], [tgcol].[name] AS [referenced_column_name], [sed].[is_schema_bound_reference], [sed].[is_ambiguous], [sed].[is_caller_dependent] FROM sys.[sql_expression_dependencies] [sed] INNER JOIN sys.[all_objects] AS [ob] ON [sed].[referencing_id] = [ob].[object_id] LEFT JOIN sys.[all_columns] [col] ON [sed].[referencing_minor_id] = [col].[column_id] AND [sed].[referencing_id] = [col].[object_id] LEFT JOIN sys.[all_objects] AS [tgob] ON [sed].[referenced_id] = [tgob].[object_id] AND [sed].[referenced_server_name] IS NULL AND ( [sed].[referenced_database_name] IS NULL OR [sed].[referenced_database_name] = DB_NAME() ) LEFT JOIN sys.[all_columns] [tgcol] ON [sed].[referenced_minor_id] = [tgcol].[column_id] AND [sed].[referenced_id] = [tgcol].[object_id] AND [sed].[referenced_server_name] IS NULL AND ( [sed].[referenced_database_name] IS NULL OR [sed].[referenced_database_name] = DB_NAME() ) WHERE [sed].[referencing_id] = CASE WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName) ELSE [sed].[referencing_id] END OR [sed].[referenced_id] = CASE WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName) ELSE [sed].[referenced_id] END ORDER BY [referencing_object_name]; |
You can also find it in my SQL Server Scripts repo as GetObjectDependencies.sql.
Object dependencies result set
The result set is fairly self-explanatory. Unfortunately, it’s a bit wider than what I can fit in a decent screenshot.
One thing to note is, that for computed columns, the referenceing_object_name column will also contain the name of that column as part of the fully qualified object name.
Note
Due to the way that sys.sql_expression_dependencies works, the referencing_database_name column can contain CROSS APPLY alias names, or, in the case of XML data type methods, aliases, CTE names or the inserted/deleted tables.
This is not due to the query itself, but due to the information returned from sys.sql_expression_dependencies.
Here’s an example from the AdventureWorks2019 database, where the Person.iuPerson trigger appears to reference a database named inserted.
| 1 2 3 4 5 6 7 | SELECT OBJECT_NAME([referencing_id]) AS [object_name], [referenced_server_name], [referenced_database_name], [referenced_schema_name], [referenced_entity_name] FROM sys.[sql_expression_dependencies] WHERE [referencing_id] = OBJECT_ID(N'Person.iuPerson'); |
But looking ad the trigger’s DDL shows that’s not the case, and it is, in fact, using the exist() XML method on the inserted.Demographics column.
The relevant portion of the code being:
| 1 2 3 4 5 6 | FROM inserted WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] AND inserted.[Demographics] IS NOT NULL AND inserted.[Demographics].exist(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey/TotalPurchaseYTD') <> 1; |
Conclusion
That’s pretty much it.
Just a query that should help you when you’re digging through database object dependencies in SQL Server.
If you have more digging to do in unfamiliar databases, you might also want to give this post a read:
2 comments
Hey Vlad
your script is picking up Cross Apply Aliases as Database names but a nice script overall
Hey George
Thank you for the feedback!
Unfortunately, that is a limitation of sys.sql_expression_dependencies.
If you run
SELECT * FROM sys.sql_expression_dependencies;
you’ll notice that the referenced_database_name column can contain things that aren’t databases, such as aliases, CTE names, etc.
One example is the Person.iuPerson trigger in the AdventureWorks2019 database where using an XML method ( inserted.[Demographics].exist() ) causes inserted to show up as a referenced database.
I’ll make a clarifying note of this in the post to help avoid confusion.