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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.