I have around 500 functions out of which 100 functions are having dependency with other functions, I mean they are getting called from other functions.

I need to find the hierarchy list of functions which are having dependency.

Expected output:

Function Name   Level
----------------------
Fn_test         0
Fn_abc          0
Fn_xa           1
Fn_zi           2 
Fn_my           3

Note:

1. Level - 0 for the independent function.
2. Level - 1 for parent function
3. Level - 2...n for child functions

CodePudding user response：

You can use sys.sql_dependencies to find out which functions calls other functions:

create function fn_a() returns int
begin
    return 0;
end;
go
create function fn_b() returns int
begin
    return dbo.fn_a();
end;
go

select f1.name, f2.name, d.* from sys.sql_dependencies d
inner join sys.objects f1 on d.object_id = f1.object_id and f1.type='FN'
inner join sys.objects f2 on d.referenced_major_id = f2.object_id and f2.type='FN'

This returns fn_b, fn_a.

Create a CTE based on this query to get the function call hierarchy.