Dependency Tree for Functions

function_dependency_tree.sql

SELECT 
	 base_sch.name AS object_schema 
	,base_obj.name AS object_name 
	,base_obj.type_desc object_type 
	 
	,ref_sch.name AS ref_schema 
	,ref_obj.name AS ref_name 
	,ref_obj.type_desc AS ref_type 

	-- ,ref_def.definition 
FROM sys.sql_expression_dependencies AS sed

-- While sys.sql_expression_dependencies is generally accurate, 
-- it may not always capture all types of dependencies, 
-- especially those involving dynamic SQL or dynamic queries generated at runtime. 
-- It primarily tracks dependencies within a single database. 
-- It may not capture dependencies between objects in different databases or instances.
-- The information in sys.sql_expression_dependencies is not automatically updated in real-time. 
-- It relies on the SQL Server's metadata cache, which can be periodically refreshed. 
-- This means that in some cases, there might be a slight delay in reflecting changes in dependencies.
-- You need appropriate permissions on the database objects you are examining. 
-- Users without the necessary permissions might not be able to access this information.
-- service packs or cumulative updates, can sometimes affect the reliability of dependency tracking. 
-- It's essential to keep your SQL Server installation up-to-date.

INNER JOIN sys.objects AS base_obj ON base_obj.object_id = sed.referencing_id 
INNER JOIN sys.schemas AS base_sch ON base_sch.schema_id = base_obj.schema_id 

INNER JOIN sys.objects AS ref_obj ON ref_obj.object_id = sed.referenced_id 
INNER JOIN sys.schemas AS ref_sch ON ref_sch.schema_id = ref_obj.schema_id 

-- LEFT JOIN sys.sql_modules AS ref_def ON ref_def.object_id = ref_obj.object_id

WHERE (1=1) 
-- AND base_obj.type_desc NOT IN ('VIEW', 'SQL_STORED_PROCEDURE', 'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION')
AND base_obj.type_desc IN ('SQL_INLINE_TABLE_VALUED_FUNCTION')
AND ref_obj.type_desc LIKE '%SQL_SCALAR_FUNCTION%'

-- USER_TABLE, VIEW, SQL_STORED_PROCEDURE
-- SQL_TABLE_VALUED_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_SCALAR_FUNCTION
-- CLR_SCALAR_FUNCTION 
-- SQL_TRIGGER, SQL_DEFAULT, CHECK_CONSTRAINT, UNIQUE_CONSTRAINT, FOREIGN_KEY_CONSTRAINT, PRIMARY_KEY_CONSTRAINT 
-- SYSTEM_TABLE, INTERNAL_TABLE, TYPE_TABLE, SQL_TYPE_TABLE


-- fu_GetLastDayOfMonth > fu_IsLeapYear > fu_dtDateSerial
-- fu_FMS_Translation > NUID
-- fu_vcDateSerial > fu_vcDateOnly
-- fu_OV_Charts_BP_BKPRoot > !!SELF!!