New views and functions for checking dependency order in SQL 2008

Checking for dependencies and dependency order in versions prior to SQL Server 2008 was not very reliable.  We had to write scripts using sp_depends and then in SQL Server 2005, we used sys.sql_dependencies but both are not reliable.  In SQL Server 2008, the support is much more robust – the only exception that we have seen so far is that the dependencies in the dynamic SQL code or the CLR code are not covered.  The new objects that are used for writing the dependency SQL are:

• sys.sql_expression_dependencies: As the name suggests, it has the object dependencies and is the replacement for the SQL 2005 version (sys.sql_dependencies).
• sys.dm_sql_referenced_entities:  This dynamic management function provides the entities that an object depends upon.
• sys.dm_sql_referencing_entites: This dynamic management function provides the entities that depend upon an object.

Another thing worth mentioning is that these also cover cross database as well as cross-instance dependencies.  Let’s create some sample objects and then see this in action:

USE DECIPHERTEST
GO
/*Create 2 sample tables*/
CREATE TABLE dbo.TEST1 (COL1 INT, COL2 NVARCHAR(10));
CREATE TABLE dbo.TEST2 (COL3 INT, COL4 NVARCHAR(10));
GO

/*First Procedure references TEST1*/
CREATE PROC dbo.TEST_PROC_1
AS
BEGIN
SELECT * FROM dbo.TEST1;
END
GO

/*First View references TEST1*/
CREATE VIEW dbo.TEST_VIEW_1
AS
SELECT col1 FROM dbo.TEST1;
GO

/*Second View references TEST2*/
CREATE VIEW dbo.TEST_VIEW_2
AS
SELECT col3 FROM dbo.TEST2;
GO

/*Second Procedure references second view*/
CREATE PROC dbo.TEST_PROC_2
AS
BEGIN
SELECT * FROM dbo.TEST_VIEW_2;
END
GO

/*Third Procedure references first view through a dynamic SQL*/
CREATE PROC dbo.TEST_PROC_3
AS
BEGIN
EXEC SP_EXECUTESQL N’SELECT * FROM TEST_VIEW_1′;
END
GO

And now, let’s write the SQL using those 3 views and DMFs that we talked about above and see the output.

SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS Referencing_Schema
,OBJECT_NAME(referencing_id)          AS Referencing_Object_Name
,referenced_schema_name              AS Referenced_Schema
,referenced_entity_name              AS Referenced_Object_Name
FROM sys.sql_expression_dependencies;

This will give this output:

Referencing_Schema    Referencing_Object_Name    Referenced_Schema    Referenced_Object_Name
--------------------------------------------------------------------------------------
dbo                    TEST_PROC_2                dbo                    TEST_VIEW_2
dbo                    TEST_VIEW_1                dbo                    TEST1
dbo                    TEST_PROC_1                dbo                    TEST1
dbo                    TEST_VIEW_2                dbo                    TEST2

And as you can see in the output above, the TEST_PROC_3 does not show up since it uses dynamic SQL.

Now, let’s use the DMFs to check for referenced and referencing sets of data.  If we want to check which objects does the procedure TEST_PROC_1 depend upon:

Referenced_Schema    Referenced_Object_Name    Referenced_Object_Minor_Name    Referenced_Object_Class
---------------------------------------------------------------------------------------------------
dbo                    TEST1                    NULL                            OBJECT_OR_COLUMN
dbo                    TEST1                    COL1                            OBJECT_OR_COLUMN
dbo                    TEST1                    COL2                            OBJECT_OR_COLUMN

And from the referencing DMF – if we want to see where the object TEST1 is referenced:

SELECT
referencing_schema_name AS Referencing_Schema,
referencing_entity_name AS Referencing_Object_Name,
referencing_class_desc  AS Referencing_Object_Class
FROM sys.dm_sql_referencing_entities(‘dbo.Test1′, ‘OBJECT’);

Referencing_Schema    Referencing_Object_Name    Referencing_Object_Class
--------------------------------------------------------------------
dbo                    TEST_PROC_1                OBJECT_OR_COLUMN
dbo                    TEST_VIEW_1                OBJECT_OR_COLUMN

All this is obviously available through the GUI as well in SSMS – right click on an object and select view dependencies:

Have to do more comprehensive tests using triggers, synonyms, cross database and cross instance dependencies and then write code to present all the dependencies together along with nesting levels, for example – a procedure uses a synonym – it should show the nesting and show the actual object that it depends on rather than just showing the synonym – no biggie … will post that code soon once it is done and tested.

About these ads