List user defined functions (UDFs) in SQL Server databsae

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


select schema_name(obj.schema_id) as schema_name, as function_name,
       case type
            when 'FN' then 'SQL scalar function'
            when 'TF' then 'SQL table-valued-function'
            when 'IF' then 'SQL inline table-valued function'
        end as type,
        substring(par.parameters, 0, len(par.parameters)) as parameters,
        TYPE_NAME(ret.user_type_id) as return_type,
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select + ' ' + 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,


  • 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)


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

Sample results

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