What are all the functions and their parameters?

File this one under: I wrote it once, may I never need it again

In my ever expanding quest for getting all the metadata, I how could I determine the metadata for all my table valued functions? No problem, that's what sys.dm_exec_describe_first_result_set is for. SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.foo(@xmlMessage)', N'@xmlMessage nvarchar(max)', 1) AS DEDFRS

Except, I need to know parameters. And I need to know parameter types. And order. Fortunately, sys.parameters and sys.types makes this easy. The only ugliness comes from the double invocation of row rollups

SELECT 
    CONCAT
    (
        ''
    ,   'SELECT * FROM '
    ,   QUOTENAME(S.name)
    ,   '.'
    ,   QUOTENAME(O.name)
    ,   '('
        -- Parameters here without type
    ,   STUFF
        (
            (
                SELECT 
                    CONCAT
                    (
                        ''
                    ,   ','
                    ,   P.name
                    ,   ' '
                    )
                FROM
                    sys.parameters AS P
                WHERE
                    P.is_output = CAST(0 AS bit)
                    AND P.object_id = O.object_id
                ORDER BY
                    P.parameter_id
                FOR XML PATH('')
            )
        ,   1
        ,   1
        ,   ''
        )

    ,   ') AS F;'
    ) AS SourceQuery
,   (
        STUFF
        (
            (
                SELECT 
                    CONCAT
                    (
                        ''
                    ,   ','
                    ,   P.name
                    ,   ' '
                    ,   CASE 
                        WHEN T2.name LIKE '%char' THEN CONCAT(T2.name, '(', CASE P.max_length WHEN -1 THEN 'max' ELSE CAST(P.max_length AS varchar(4)) END, ')')
                        WHEN T2.name = 'time' OR T2.name ='datetime2' THEN CONCAT(T2.name, '(', P.scale, ')')
                        WHEN T2.name = 'numeric' THEN CONCAT(T2.name, '(', P.precision, ',', P.scale, ')')
                        ELSE T2.name
                    END
                    )
                FROM
                    sys.parameters AS P
                    INNER JOIN
                        sys.types AS T2
                        ON T2.user_type_id = P.user_type_id
                WHERE
                    P.is_output = CAST(0 AS bit)
                    AND P.object_id = O.object_id
                ORDER BY
                    P.parameter_id
                FOR XML PATH('')
            )
        ,   1
        ,   1
        ,   ''
        )
    ) AS ParamterList
FROM
    sys.schemas AS S
    INNER JOIN
        sys.objects AS O
        ON O.schema_id = S.schema_id
WHERE
    O.type IN ('FT','IF', 'TF');

How you use this is up to you. I plan on hooking it into the Biml Query Table Builder to simulate tables for all my TVFs.