Technical Article

Find function dependencies

,

This script creates a table of functions.

Then uses the sys.dm_sql_referencing_entities table function to find all the dependent objects for each function.

The result is a table of functions with empty columns if the are no dependent objects or details of each dependent object.

/* TEMP TABLE OF FUNCTIONS */-- DROP TABLE #F
SELECT  ROUTINE_NAME ,
        ROUTINE_SCHEMA ,
        DATA_TYPE ,
        '[' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']' AS RoutineSchemaName ,
        CONVERT(INT, 0) AS inProcess
INTO    #F
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   ROUTINE_TYPE = 'FUNCTION'
/* ADD SPECIFIC FUNCTION NAME if required etc */-- AND ROUTINE_NAME LIKE 'something' 
ORDER BY ROUTINE_NAME

-- RESULTS TABLE OF FUNCTION AND DEPENDENCIES
CREATE TABLE #Dep
    (
      SchemaFunction sysname NOT NULL ,
      referencing_schema_name sysname NOT NULL ,
      referencing_entity_name sysname NOT NULL ,
      referencing_id INT NOT NULL ,
      referencing_class TINYINT NULL ,
      referencing_class_desc NVARCHAR(60) NULL ,
      is_caller_dependent BIT NOT NULL
    )

DECLARE @SchemaFunction NVARCHAR(500) 

WHILE EXISTS ( SELECT   *
               FROM     #F
               WHERE    inProcess = 0 )
    BEGIN

        SELECT TOP 1
                @SchemaFunction = RoutineSchemaName
        FROM    #F
        WHERE   inProcess = 0
        ORDER BY RoutineSchemaName

        INSERT  INTO #Dep
                SELECT  @SchemaFunction AS SchemaFunction ,
                        referencing_schema_name ,
                        referencing_entity_name ,
                        referencing_id ,
                        referencing_class ,
                        referencing_class_desc ,
                        is_caller_dependent
                FROM    sys.dm_sql_referencing_entities(@SchemaFunction,
                                                        'OBJECT');  

        UPDATE  #F
        SET     inProcess = 1
        WHERE   RoutineSchemaName = @SchemaFunction

    END

SELECT  ROUTINE_NAME ,
        ROUTINE_SCHEMA ,
        DATA_TYPE ,
        RoutineSchemaName ,
        inProcess ,
        SchemaFunction ,
        referencing_schema_name ,
        referencing_entity_name ,
        referencing_id ,
        referencing_class ,
        referencing_class_desc ,
        is_caller_dependent
FROM    #F
        LEFT JOIN #Dep ON #F.RoutineSchemaName = #Dep.SchemaFunction

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating