List of all (User defined scalar) functions and their dependencies

Question

I'm trying to select a list of all the functions in the database and their dependencies.

(The dependencies that would appear if I were to view the dependencies manually and select the 'Objects that depend on [function]' option.)

My main problem is getting all the dependencies to roll up into one row per function.

I've tried using stuff, but for some reason I can't get it to work in this context.

select 
    o.name
    , stuff((select N', ' + Name from sys.objects o3 where o3.object_id = o2.object_id for xml path ('')), 1, 1, N'') as Dependencies
from sys.objects o
    left join sys.sql_expression_dependencies sed
    on object_id = referenced_id
    left join sys.objects o2
    on referencing_id = o2.object_id
where o.type='FN' 
order by o.name

Where am I going wrong?

Answer 1

This might work for you:

SELECT
     O.Name
    ,T.Dependencies
FROM sys.Objects O
OUTER APPLY
(
    SELECT SUBSTRING(
        (
        SELECT ',' + OBJECT_NAME(D.referenced_id)
        FROM sys.SQL_Expression_Dependencies D
        WHERE D.referencing_id = O.Object_ID
        GROUP BY OBJECT_NAME(D.referenced_id)
        ORDER BY OBJECT_NAME(D.referenced_id)
        FOR XML PATH('')
        )
    ,2,4000) AS Dependencies
) T
WHERE O.Type = 'FN'
ORDER BY O.Name
;

If not, update me about what's missing and I can go from there. Good luck! :)