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?

Answer 1

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/

Answer 2

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...