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