table-valued functions for obtaining a list of dependent objects

USE AdventureWorks2008;

GO 

-- DROP FUNCTION fnDependentObjectList

CREATE FUNCTION fnDependentObjectList ( @Objectname sysname)

RETURNS @buffer table (Given sysname, [Dependent] sysname)

AS

BEGIN;

WITH cteDependence (DependentObjectID, DependentObjectName, GivenObjectName, GivenObjectID)

        AS

        (

        SELECT DISTINCT

               d.object_id,

               OBJECT_NAME(d.object_id),

               GivenObject = OBJECT_NAME(d.referenced_major_id),

               GivenObjectID = d.referenced_major_id

        FROM   

               sys.sql_dependencies d

               JOIN sys.objects so

               ON d.referenced_major_id = so.object_id

        WHERE  

               d.referenced_major_id = object_id(@ObjectName)

        UNION ALL

        SELECT

               d.object_id,

               OBJECT_NAME(d.object_id),

               OBJECT_NAME(referenced_major_id),

               object_id

        FROM   

                   sys.sql_dependencies d

            JOIN cteDependence dep

            ON d.referenced_major_id = dep.DependentObjectID 

        WHERE

               d.referenced_major_id <> d.object_id 

        )

        INSERT @buffer

        SELECT DISTINCT

               GivenObjectName, DependentObjectName

       FROM   

               cteDependence d

    RETURN

END

GO

            

SELECT * FROM fnDependentObjectList ('Production.Product')

GO

-- OR

-- SQL Server 2008 object dependency query - listing object dependencies

SELECT ReferencingObjectType = o1.type,

       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,

       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,

       ReferencedObjectType = o2.type

FROM   AdventureWorks2008.sys.sql_expression_dependencies ed

       INNER JOIN  AdventureWorks2008.sys.objects o1

         ON ed.referencing_id = o1.object_id

       INNER JOIN AdventureWorks2008.sys.objects o2

         ON ed.referenced_id = o2.object_id

WHERE o1.type in ('P','TR','V', 'TF')

ORDER BY ReferencingObjectType, ReferencingObject