List user defined functions (UDFs) in Azure SQL databsae

Bart Gawrych - Dataedo Team Bart Gawrych 2019-03-04

Table of Contents:


    Query below return all User Defined Functions and information about it in Azure SQL database.

    Query

    select schema_name(obj.schema_id) as schema_name,
           obj.name as function_name,
           case type
                when 'FN' then 'SQL scalar function'
                when 'TF' then 'SQL inline table-valued function'
                when 'IF' then 'SQL table-valued-function'
            end as type,
            substring(par.parameters, 0, len(par.parameters)) as parameters,
            TYPE_NAME(ret.user_type_id) as return_type,
            mod.definition
    from sys.objects obj
    join sys.sql_modules mod
         on mod.object_id = obj.object_id
    cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', ' 
                 from sys.parameters p
                 where p.object_id = obj.object_id 
                       and p.parameter_id != 0 
                for xml path ('') ) par (parameters)
    left join sys.parameters ret
              on obj.object_id = ret.object_id
              and ret.parameter_id = 0
    where obj.type in ('FN', 'TF', 'IF')
    order by schema_name,
             function_name;
    

    Columns

    • schema_name - schema name
    • function_name - function name
    • type - type of UDF:
      • SQL scalar function
      • SQL inline table-valued function
      • SQL table-valued-function
    • parameters - name of parameters and their data types
    • return_type - for scalar function data type of returning scalar value
    • definition - function definition (CREATE FUNCTION statement)

    Rows

    • One row represents one function
    • Scope of rows: all user defined functions in database
    • Ordered by schema name and function name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept