none
SQL Server 2008 Dependencies

    Question

  • So, I've used R-click/Dependencies to see what is dependent on a selected entity. All well and good, but I'd like to print the tree or save it to the clipboard. I can't find any button or context menu to allow copying the dependency tree.

    I looked on the 'net and found some code saying to use "sql_expression_dependencies," but, when I do, I get, "Invalid object name 'sql_expression_dependencies'."

    Once again, I figure my noobidity is causing me to overlook something. I figure there must be some way to grab the result off the Object Dependencies dialog, but I'm just not finding it. I mean, looking at it is just fine and dandy, but that's of limited use; I need to be able to put the result into another app, like Word or Excel.

    Any thoughts?

    Monday, February 27, 2012 7:24 PM

Answers

  • USE master
    GO
    CREATE PROCEDURE sp_FindDependencies
    (
            @ObjectName SYSNAME,
            @ObjectType VARCHAR(5) = NULL
    )
    AS
    BEGIN
        DECLARE @ObjectID AS BIGINT 


            SELECT TOP(1) @ObjectID = object_id
            FROM sys.objects
            WHERE name = @ObjectName
            AND type = ISNULL(@ObjectType, type) 


        SET NOCOUNT ON ; 


          WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
            AS
            (
            SELECT DISTINCT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
                   ReferencedObjectID = sd.referenced_major_id
            FROM    
                   sys.sql_dependencies sd
                   JOIN sys.objects so ON sd.referenced_major_id = so.object_id
            WHERE   
                   sd.referenced_major_id = @ObjectID
            UNION ALL
            SELECT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   OBJECT_NAME(referenced_major_id),
                   object_id
            FROM    
                   sys.sql_dependencies sd
                JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID       
            WHERE
                   sd.referenced_major_id <> sd.object_id     
            )
            SELECT DISTINCT
                   DependentObjectName
            FROM   
                   DependentObjectCTE c
    END
    ----Usage
    EXECUTE sp_ms_marksystemobject \'sp_FindDependencies\'
    Now I can call my new system stored procedure to find any objects that are dependent upon the SalesHistory 
    table that I just created.


    EXECUTE sp_FindDependencies \'SalesHistory\'

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 28, 2012 5:30 AM
    Moderator

All replies

  • USE master
    GO
    CREATE PROCEDURE sp_FindDependencies
    (
            @ObjectName SYSNAME,
            @ObjectType VARCHAR(5) = NULL
    )
    AS
    BEGIN
        DECLARE @ObjectID AS BIGINT 


            SELECT TOP(1) @ObjectID = object_id
            FROM sys.objects
            WHERE name = @ObjectName
            AND type = ISNULL(@ObjectType, type) 


        SET NOCOUNT ON ; 


          WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
            AS
            (
            SELECT DISTINCT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
                   ReferencedObjectID = sd.referenced_major_id
            FROM    
                   sys.sql_dependencies sd
                   JOIN sys.objects so ON sd.referenced_major_id = so.object_id
            WHERE   
                   sd.referenced_major_id = @ObjectID
            UNION ALL
            SELECT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   OBJECT_NAME(referenced_major_id),
                   object_id
            FROM    
                   sys.sql_dependencies sd
                JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID       
            WHERE
                   sd.referenced_major_id <> sd.object_id     
            )
            SELECT DISTINCT
                   DependentObjectName
            FROM   
                   DependentObjectCTE c
    END
    ----Usage
    EXECUTE sp_ms_marksystemobject \'sp_FindDependencies\'
    Now I can call my new system stored procedure to find any objects that are dependent upon the SalesHistory 
    table that I just created.


    EXECUTE sp_FindDependencies \'SalesHistory\'

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 28, 2012 5:30 AM
    Moderator
  • Whoa!

    There is no simple "print" or "copy" from the dependency tree?

    Thanx, Uri, I'll try that out, but I would have thought that Microsoft would have provided a means to do it from a context menu or some such...

    Tuesday, February 28, 2012 3:38 PM