Reporting SQL Dependencies
SQL dependencies are the by-name references that are used in SQL expressions that make one user-defined entity reliant on another entity. For example, views and stored procedures depend upon the existence of tables that contain the data returned by the view or procedure. Reporting on dependency information is useful in the following scenarios:
Moving a module such as a stored procedure from one application to another.
Before moving a module, you can determine if there are any database or cross-database entities referenced by the module that must also be moved along with the module.
Modifying the definition of an entity, such as adding or dropping a column in a table.
Before modifying an entity, you can determine if there are other entities that depend on the current definition of the entity. These dependent entities may produce unexpected results when queried or invoked after the modification and may require a metadata refresh operation or a modification to their definition.
Moving one or more databases from one server to another.
Before moving databases to another server, you can determine whether entities in one database have dependencies on entities in another database. In this way, you know to move those databases to the same server.
Configuring failover for applications that span multiple databases.
You want to ensure that your application is available at all times and use database mirroring as your failover strategy. The application depends on more than one database and you want to make sure that the application can run successfully it if fails over to the mirror server. Because mirroring works at the database level, you need to determine which databases are critical to the application, so that mirroring is set up for all of them individually. You can then ensure that all databases fail over together, thus ensuring that the application works on the mirror server.
Finding entities in an application that execute distributed queries using four-part names.
You want to know which linked servers are being used in distributed queries.
Finding entities used in an application that contain caller-dependent references or one-part name references.
Before deploying an application, you can determine whether entities used by the application contain caller-dependent references or references to entities using only a one-part name. Such references indicate poor programming practices and can result in unexpected behavior when the application is deployed. This is because the resolution (binding) of the referenced entity depends on the schema of the caller and this information is not determined until run time. After finding these references, the queries can be corrected by specifying the appropriate multipart name such as schema_name.object_name.
For more information about SQL dependencies, see Understanding SQL Dependencies.
To view SQL dependencies, SQL Server 2008 provides the sys.sql_expression_dependencies catalog view and the sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities dynamic management functions. You can query these objects to return dependency information on user-defined entities.
SQL dependencies can also be viewed by using View Dependencies in SQL Server Management Studio. For more information, see How to: View SQL Dependencies (SQL Server Management Studio).
Using the sys.sql_expression_dependencies Catalog View
The sys.sql_expression_dependencies catalog view provides the database owner or database administrator with the ability to report dependency information for a given database. Using this view, you can answer global questions such as the following:
What cross-server or cross-database dependencies does the database have?
What dependencies exist within the database?
Which entities in the database have caller-dependent references?
What server-level or database-level DDL triggers have dependencies on entities in the database?
Which modules in the database use a user-defined type (UDT)?
sys.sql_expression_dependencies has the following limitations:
Dependencies on cross-server and cross-database entities are returned only when a valid four-part or three-part name is specified. IDs for the referenced entities are not returned.
Column-level dependencies are reported only for schema-bound entities.
Using the sys.dm_sql_referenced_entities Dynamic Management Function
The sys.dm_sql_referenced_entities function returns one row for each user-defined entity referenced by name in the definition of the specified referencing entity. The referencing entity can be a user-defined object, server-level DDL trigger, or database-level DDL trigger. This is the same information that is returned by sys.sql_expression_dependencies, however, the result set is limited to the entities that are referenced by the specified referencing entity. This function is useful for developers who want to track dependencies on the modules they own or on which they have VIEW DEFINITION permission.
Using the sys.dm_sql_referencing_entities Dynamic Management Function
The sys.dm_sql_referencing_entities function returns one row for each user-defined entity in the current database that references another user-defined entity by name. The referencing entity can be a user-defined object, type (alias or CLR UDT), XML schema collection, or partition function. This function is useful for developers who want to track dependencies on the entities they own. For example, before modifying a user-defined type, a developer can use this function to determine all the entities in the database that depend on that type. Note that references to a user-defined type in a table are not reported unless the type is specified in the definition of a computed column, CHECK constraint, or DEFAULT constraint.
The following examples return SQL dependencies by using the sys.sql_expression_dependencies catalog view and the sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities dynamic management functions.
Reporting the Entities on Which a Specified Entity Depends
You can query the sys.sql_expression_dependencies catalog view or the sys.dm_sql_referenced_entities dynamic management function to return a list of entities on which a specified entity depends. For example, you can return a list of entities that are referenced by a module such as a stored procedure or trigger.
The following example creates a table, a view, and three stored procedures. These objects are used in later queries to demonstrate how to report dependency information. Observe that MyView and MyProc3 both reference Mytable. MyProc1 references MyView, and MyProc2 references MyProc1.
USE AdventureWorks2008R2; GO -- Create entities CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32)); GO CREATE VIEW dbo.MyView AS SELECT c1, c2 FROM dbo.MyTable; GO CREATE PROC dbo.MyProc1 AS SELECT c1 FROM dbo.MyView; GO CREATE PROC dbo.MyProc2 AS EXEC dbo.MyProc1; GO CREATE PROC dbo.MyProc3 AS SELECT * FROM AdventureWorks2008R2.dbo.MyTable; EXEC dbo.MyProc2; GO
The following example queries the sys.sql_expression_dependencies catalog view to return the entities that are referenced by MyProc3.
USE AdventureWorks2008R2; GO SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name ,referenced_server_name AS server_name ,referenced_database_name AS database_name ,referenced_schema_name AS schema_name , referenced_entity_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3'); GO
Here is the result set.
referencing_entity server_name database_name schema_name referenced_entity
------------------ ----------- -------------------- ----------- -----------------
MyProc3 NULL NULL dbo MyProc2
MyProc3 NULL AdventureWorks2008R2 dbo MyTable
(2 row(s) affected)
The two entities referenced by-name in the definition of MyProc3 are returned. The server name is NULL because the referenced entities are not specified using a valid four-part name. The database name is shown for MyTable because the entity was defined in the procedure using a valid three-part name.
Similar information can be returned by using the sys.dm_sql_referenced_entities. In addition to reporting object names, this function can be used to return column-level dependencies on both schema-bound and non-schema-bound entities. The following example returns the entities on which MyProc3 depends including column-level dependencies.
USE AdventureWorks2008R2; GO SELECT referenced_server_name AS server , referenced_database_name AS database_name , referenced_schema_name AS schema_name , referenced_entity_name AS referenced_entity , referenced_minor_name AS column_name FROM sys.dm_sql_referenced_entities ('dbo.MyProc3', 'OBJECT'); GO
Here is the result set.
server_name database_name schema_name referenced_entity column_name
----------- -------------------- ----------- ----------------- -----------
NULL NULL dbo MyProc2 NULL
NULL AdventureWorks2008R2 dbo MyTable NULL
NULL AdventureWorks2008R2 dbo MyTable c1
NULL AdventureWorks2008R2 dbo MyTable c2
(4 row(s) affected)
In this result set, the same two entities are returned, however, two additional rows are returned showing the dependency on columns c1 and c2 in MyTable. Notice that in the definition of MyProc3, a SELECT * statement was used to reference the columns in MyTable. This is not a recommended coding practice; however, the column-level dependencies are still tracked by the Database Engine.
Up to this point, the examples have illustrated how to return the entities on which an entity depends directly. The following example uses a recursive common table expression (CTE) to return all direct and indirect dependencies on an entity.
DECLARE @referencing_entity AS sysname; SET @referencing_entity = N'MyProc3'; WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level) AS ( SELECT entity_name = CASE referencing_class WHEN 1 THEN OBJECT_NAME(referencing_id) WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t WHERE t.object_id = sed.referencing_id) WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st WHERE st.object_id = sed.referencing_id) COLLATE database_default END ,referenced_schema_name ,referenced_entity_name ,referenced_id ,0 AS level FROM sys.sql_expression_dependencies AS sed WHERE OBJECT_NAME(referencing_id) = @referencing_entity UNION ALL SELECT entity_name = CASE sed.referencing_class WHEN 1 THEN OBJECT_NAME(sed.referencing_id) WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t WHERE t.object_id = sed.referencing_id) WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st WHERE st.object_id = sed.referencing_id) COLLATE database_default END ,sed.referenced_schema_name ,sed.referenced_entity_name ,sed.referenced_id ,level + 1 FROM ObjectDepends AS o JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id ) SELECT entity_name,referenced_schema, referenced_entity, level FROM ObjectDepends ORDER BY level; GO
Here is the result set.
entity_name referenced_schema referenced_entity level
----------- ----------------- ----------------- -----
MyProc3 dbo MyProc2 0
MyProc3 dbo MyTable 0
MyProc2 dbo MyProc1 1
MyProc1 dbo MyView 2
MyView dbo MyTable 3
(5 row(s) affected)
In this result set, MyProc2 and MyTable are returned as direct dependencies, as indicated by the level 0 value. The third row shows the indirect dependency on MyProc1, which is referenced in the definition of MyProc2. The fourth row shows the dependency on MyView, which is referenced in the definition of MyProc1 and finally, a dependency on MyTable, which is referenced in the definition of MyView.
By returning hierarchical dependency information, you can determine the complete list of direct and indirect dependencies on a given entity and infer the order in which these objects should be deployed if they need to be moved to another database.
The following example returns the same hierarchical dependency information using the sys.dm_sql_referenced_entities function. The entities on which MyProc3 depends are returned including column-level dependencies.
USE AdventureWorks2008R2; GO DECLARE @entity AS sysname , @type AS sysname; SET @entity = N'dbo.MyProc3'; SET @type = N'OBJECT'; WITH ObjectDepends(referenced_schema_name, referenced_entity_name, referenced_column, referenced_id,level) AS ( SELECT referenced_schema_name ,referenced_entity_name ,referenced_minor_name AS referenced_column ,referenced_id ,0 AS level FROM sys.dm_sql_referenced_entities (@entity, @type) UNION ALL SELECT re.referenced_schema_name ,re.referenced_entity_name ,re.referenced_minor_name AS referenced_column ,re.referenced_id ,level + 1 FROM ObjectDepends AS o CROSS APPLY sys.dm_sql_referenced_entities (o.referenced_schema_name + N'.' + o.referenced_entity_name, @type) AS re ) SELECT referenced_schema_name, referenced_entity_name, referenced_column, level FROM ObjectDepends ORDER BY level; GO
Reporting on the Entities that Depend on a Specified Entity
You can query the sys.sql_expression_dependencies catalog view or the sys.dm_sql_referencing_entities dynamic management function to return a list of entities that depend on the specified entity. For example, if the specified entity is a table, all entities that reference that table by name in their definition are returned.
The following example returns the entities that reference the entity dbo.MyTable.
USE AdventureWorks2008R2; GO SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name, OBJECT_NAME(referencing_id) AS referencing_entity_name, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_column, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed WHERE referenced_id = OBJECT_ID(N'dbo.MyTable'); GO
Similar information can be returned by using the sys.dm_sql_referenced_entities dynamic management function.
Reporting Column-Level Dependencies
Column-level dependencies can be reported by using sys.dm_sql_referenced_entities for both schema-bound and non-schema-bound entities. Column-level dependencies on schema-bound entities can also be reported by using sys.sql_expression_dependencies.
The following example queries sys.dm_sql_referenced_entities to report column-level dependencies on non-schema-bound entities. The example first creates Table1 and Table 2 and stored procedure Proc1. The procedure references columns b and c in Table1 and column c2 in Table2. The view sys.dm_sql_referenced_entities is run with the stored procedure specified as the referencing entity. The result set contains rows for the referenced entities Table1 and Table2 and the columns that are referenced in the definition of the stored procedure. Notice that NULL is returned in the column_name column in the rows that reference the tables.
USE AdventureWorks2008R2; GO CREATE TABLE dbo.Table1 (a int, b int, c int); GO CREATE TABLE dbo.Table2 (c1 int, c2 int); GO CREATE PROCEDURE dbo.Proc1 AS SELECT b, c FROM dbo.Table1; SELECT c2 FROM dbo.Table2; GO SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS column_name FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT'); GO
Here is the result set.
referenced_id, table_name, column_name
------------- ----------- -------------
151671588 Table1 NULL
151671588 Table1 b
151671588 Table1 c
2707154552 Table2 NULL
2707154552 Table2 c2
Reporting Cross-Server and Cross-Database Dependencies
A cross-database dependency is created when an entity makes a reference to another entity by using a valid three-part name. A cross-server reference is created when an entity makes a reference to another entity by using a valid four-part name. The name of the server and database are recorded only when the name is explicitly specified. For example, when specified as MyServer.MyDB.MySchema.MyTable, the server and database names are recorded; however, when specified as MyServer..MySchema.MyTable, only the server name is recorded. For more information about how cross-server and cross-database dependencies are tracked, see Understanding SQL Dependencies.
Cross-database and cross-server dependencies can be reported by using sys.sql_expression_dependencies or sys.dm_sql_referenced_entitites.
The following example returns all cross-database dependencies. The example first creates the database db1 and two stored procedures that reference tables in the databases db2 and db3. The sys.sql_expression_dependencies table is then queried to report the cross-database dependencies between the procedures and the tables. Notice that NULL is returned in the referenced_schema_name column for the referenced entity t3 because a schema name was not specified for that entity in the definition of the procedure.
CREATE DATABASE db1; GO USE db1; GO CREATE PROCEDURE p1 AS SELECT * FROM db2.s1.t1; GO CREATE PROCEDURE p2 AS UPDATE db3..t3 SET c1 = c1 + 1; GO SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE referenced_database_name IS NOT NULL; GO USE master; GO DROP DATABASE db1; GO
Reporting Caller-Dependent References
A caller-dependent reference means that the schema binding of the referenced entity occurs at run time; therefore, resolution of the entity ID depends on the default schema of the caller. This is commonly called dynamic schema binding and occurs when the referenced entity is a stored procedure, extended stored procedure, or a non-schema-bound user-defined function called in an EXECUTE statement without specifying a schema name. For example, a reference to an entity in the format EXECUTE MySchema.MyProc is not caller dependent; however, a reference in the format EXECUTE MyProc is caller dependent.
Caller-dependent references may cause unexpected behavior when the module in which it is referenced is executed. For example, consider the following stored procedure, which references a procedure by using a one-part name.
When Proc1 is executed, Proc2 binds to the schema of the caller. Assume that Proc1 is executed by User1, who has a default schema of S1 and executed by User2, who has a default schema of S2. When Proc1 is executed by User1, the referenced entity resolves to S1.Proc2. When Proc1 is executed by User2, the referenced entity resolves to S2.Proc2. Because of this behavior, the ID of Proc2 cannot be resolved until Proc1 is executed; therefore the is_caller_dependent column is set to 1 in the sys.sql_expression_dependencies view and sys.dm_sql_referenced_entities function. When Proc1 is executed, the Database Engine will look for the referenced entity Proc2 in the default schema of the caller. If it is not found, the dbo schema is checked. If Proc2 cannot be found in the dbo schema, the ID of Proc2 cannot be resolved and the statement fails. We recommend specifying two-part names when referencing database entities to eliminate potential application errors.
The following example returns each entity in the current database that contains a caller-dependent reference.
Reporting on the Entities that Use a Specified UDT
The following example returns each entity in the current database that references the specified type in its definition. The result set shows that two stored procedures use this type. The type is also used in the definition of several columns in the HumanResources.Employee table; however, because the type is not in the definition of a computed column, CHECK constraint, or DEFAULT constraint in the table, no rows are returned for the table.
Reporting Server-Level DDL Trigger Dependencies
You can report on the dependencies of server-level DDL triggers by using sys.sql_expression_dependencies and sys.dm_sql_referencing_entities only when the context is set to the master database. When using the sys.dm_sql_referenced_entities function, the context can be any database.
The following example queries the sys.sql_expression_dependenciesview to report on server-level DDL trigger dependencies.