Using Object Dependencies in SQL Server 2008

Started by VelMurugan, Oct 14, 2008, 10:49 AM

Previous topic - Next topic

VelMurugan

Using Object Dependencies in SQL Server 2008

Previous versions of SQL Server were not that great at keeping track of object dependencies.  The reason for this was due to the fact that all object dependencies were tracked by the ID of the object, which meant that the object had to exist first.  SQL Server 2008 greatly expands on this capability by tracking objects by the name of the object, rather than relying upon the ID.  Advantages of this approach are that object dependencies remain tracked even after some objects have been removed from the database, as well as are tracked even if an object has yet to be created.

New Management Objects

SQL Server 2008 introduces two new dynamic management functions and a new system view for keeping track of object dependencies.  These new objects hold information stored by the Database Engine for dependencies created when objects are created, altered, or dropped.  A dependency is created between two objects when one object appears by name inside a SQL expression stored in another object.  The object that appears inside the expression is known as the referenced entity, while the object that houses the SQL expression is known as the referencing entity.

sys.sql_expression_dependencies

This view holds one record for each dependency on a user-defined object in the current database.  These user-defined objects can be objects stored in the current database, objects stored in a different database and referenced using a three part naming convention (databasename.schemaname.objectname), objects on a different server and referenced via a linked server using a four part naming convention (servername.databasename.schemaname.objectname), and objects that do not exist at the time the object is created (known as deferred objects).

sys.dm_sql_referenced_entities

This function returns one row for each user defined object referenced by name in the definition of the specified referencing entity.  For example, if the view vw_SampleView references Field1, Field2, and Field3 of the table Table1, then four rows will be returned; three for the referenced fields and one for the table reference.

sys.dm_sql_referencing_entities

This function returns one record for each user defined object in the current database that references another user defined object by name.  For example, if the view vw_SampleView references Table1 and Table2, then two records will be returned by this function; one for each table reference.

An Example

Let's look at an example of how dependencies are tracked inside the database by creating some objects and querying the new DMVs.  Use the script below to create a table named SalesHistory.

CREATE TABLE [dbo].[SalesHistory](

      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

      [Product] [char](150) NULL,

      [SaleDate] [datetime] NULL,

      [SalePrice] [money] NULL

)


In the following script, I create a stored procedure named dbo.usp_GetSales.  This procedure references the SalesHistory table created in the above script.

CREATE PROCEDURE dbo.usp_GetSales

(

@Product VARCHAR(10)

)

AS

BEGIN

      SELECT COUNT(SaleID) AS SalesCount, SUM(SalePrice) AS SalesAmount

      FROM dbo.SalesHistory sh

END


The following script uses the table-valued function sys.dm_sql_referenced_returns function to find all objects that reference the SalesHistory table.  This query will not only return references to the SalesHistory table, but also includes the fields from the SalesHistory table that are referenced.  Note the use of the CROSS APPLY operator.  This is due to the fact that entities is a table-valued function and the APPLY operator is required when passing field values to table-valued functions.  Note that a call to the sys.dm_referenced_entities function will error if it encounters a reference to an entity field that does not exist.

SELECT ReferencedEntityName = o.name, g.referenced_entity_name, referenced_minor_name

FROM sys.objects o

JOIN sys.schemas s on o.schema_id = s.schema_id

CROSS APPLY sys.dm_sql_referenced_entities(s.name + '.' + o.name, 'OBJECT') g

WHERE referenced_entity_name = 'SalesHistory'


The following script queries the new dynamic management view sys.dm_sql_referencing_entities to find any objects referenced by the procedure usp_GetSales.

SELECT

ReferencingObject = referencing_entity_name,

ReferencedObject = o.name

FROM sys.objects o

JOIN sys.schemas s on o.schema_id = s.schema_id

CROSS APPLY sys.dm_sql_referencing_entities(s.name + '.' + o.name, 'OBJECT') g

WHERE

      referencing_entity_name = 'usp_GetSales'


In the following script, I create a new stored procedure named usp_GetSalesFromArchive, which references the nonexistent SalesHistoryArchive table.

CREATE PROCEDURE usp_GetSalesFromArchive

(

@Product VARCHAR(10)

)

AS

BEGIN

SELECT COUNT(SaleArchiveID) AS SalesCount, SUM(SaleArchivePrice) AS SalesAmount

      FROM dbo.SalesHistoryArchive sh

END


I can now use the sys.sql_expression_dependencies system view to find any objects that reference the SalesHistoryArchive table.

Alternatively, I can alter this same query just a bit to find the tables referenced by the usp_GetSalesFromArchive stored procedure.

SELECT ReferencingObject = o.name ,

ReferencedObject = d.referenced_entity_name

FROM sys.sql_expression_dependencies d

join sys.objects o on d.referencing_id = o.object_id

WHERE o.name = 'usp_GetSalesFromArchive'