Need a programatic way to get the names, arguments and return types for SQL Server built-in functions

Question

For postgres it is possible to obtain metadata for built-in functions via SQL queries like

SELECT 
    proname as name,
    pg_catalog.pg_get_function_arguments(pg_proc.oid) as input,
    format_type(pg_proc.prorettype, null) as returntype,
    prosrc as definition
FROM pg_proc;

However for SQL Server, I cannot find any such set of tables or functions. Is it possible?

Answer 1

This should give you what you're looking for... Plus maybe a little extra. ;)

SELECT 
    o.name,
    o.object_id,
    o.type_desc,
    px.parameters,
    returns = ISNULL(rx.returns, 'table'),
    tc.table_cols
FROM
    sys.objects o WITH (NOLOCK)
    CROSS APPLY (
        SELECT parameters = STUFF((
            SELECT 
                CONCAT(', ', p.name, ' ', TYPE_NAME(p.user_type_id), ' ', psl.precision_scale_len)
            FROM
                sys.parameters p WITH (NOLOCK)
                CROSS APPLY ( VALUES (CASE 
                        WHEN p.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
                        WHEN p.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), p.precision) + ',' + CONVERT(VARCHAR(10), p.scale) + ')'
                        WHEN p.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), p.scale) + ')'
                        WHEN p.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN p.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), p.max_length) END + ')'
                        WHEN p.user_type_id IN (231,239) THEN '(' + CASE WHEN p.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), p.max_length / 2) END + ')'
                    END) ) psl (precision_scale_len)
            WHERE 
                o.object_id = p.object_id
                AND p.is_output = 0
            FOR XML PATH ('')
            ), 1, 2, '')
        ) px (parameters)
    OUTER APPLY (
        SELECT 
            CONCAT(r.name, ' ', TYPE_NAME(r.user_type_id), ' ', psl.precision_scale_len)
        FROM
            sys.parameters r WITH (NOLOCK)
            CROSS APPLY ( VALUES (CASE 
                    WHEN r.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
                    WHEN r.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), r.precision) + ',' + CONVERT(VARCHAR(10), r.scale) + ')'
                    WHEN r.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), r.scale) + ')'
                    WHEN r.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN r.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), r.max_length) END + ')'
                    WHEN r.user_type_id IN (231,239) THEN '(' + CASE WHEN r.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), r.max_length / 2) END + ')'
                END) ) psl (precision_scale_len)
        WHERE 
            o.object_id = r.object_id
            AND r.is_output = 1
        ) rx ([returns])
    CROSS APPLY (
        SELECT STUFF((
            SELECT 
                CONCAT(', ', c.name, ' ', TYPE_NAME(c.user_type_id), ' ', psl.precision_scale_len)
            FROM
                sys.columns c WITH (NOLOCK)
            CROSS APPLY ( VALUES (CASE 
                    WHEN c.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
                    WHEN c.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), c.precision) + ',' + CONVERT(VARCHAR(10), c.scale) + ')'
                    WHEN c.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), c.scale) + ')'
                    WHEN c.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), c.max_length) END + ')'
                    WHEN c.user_type_id IN (231,239) THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), c.max_length / 2) END + ')'
                END) ) psl (precision_scale_len)
            WHERE 
                o.object_id = c.object_id
            FOR XML PATH('')), 1, 2, '')
            ) tc (table_cols)
WHERE 
    o.type IN ('AF','FN','FS','FT','IF');