SQL SERVER – Find All The User Defined Functions (UDF) in a Database

Following script is very simple script which returns all the User Defined Functions for particular database.
USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Find Great Job with Great Pay
Next Post
SQL SERVER – FIX : ERROR : Cannot find template file for new query (C:\Program Files\Microsoft SQL Server\90\Tools\ Binn\VSShell\Common7\ IDE\sqlworkbenchprojectitems\Sql\ SQLFile.sql)

Related Posts

23 Comments. Leave new

  • through SQL query, i’ve to concatenate multiple rows into single row with comma-separator. Help me how to write a coding for it.

    Ex. the column is like wise
    ID Name
    1 Arthi
    2 Preethi
    3 Madhu

    now it should be Arthi,Preethi,Madhu in a single row.

    Reply
    • declare @s varchar(100)
      set @s=”
      select @s=@s+description+’,’ from gender
      if len(@s)>0
      select left(@s,len(@s)-1)

      Reply
  • Other method

    select specific_name,specific_schema from information_schema.routines
    where routine_type=’function’

    Reply
  • Arthi

    Try this

    declare @s varchar(8000)
    select @s=coalesce(@s+’,’,”)+name from your_table
    select @s

    Also, your question is no way related to this article
    If you have any general question to ask post at http://www.sqlserverperformance.com/forum

    Reply
  • I need to be able to take the results of your query and make a script to recreate all those functions on another server…is that possible?

    Reply
  • I thinks this will also work

    SELECT b.name fncSchema, a.Name AS function_name
    FROM sys.objects a
    INNER JOIN sys.schemas b
    ON a.schema_id = b.schema_id
    WHERE TYPE in (‘FN’, ‘IF’)

    Reply
  • @Kalpesh – it’s actually

    SELECT b.name fncSchema, a.Name AS function_name, TYPE
    FROM sys.objects a
    INNER JOIN sys.schemas b
    ON a.schema_id = b.schema_id
    WHERE TYPE in (‘FN’, ‘IF’, ‘TF’)

    Reply
  • Is there any such function that check exactly Numeric values. isnumeric() is there but it arithmetic operators as numeric like * , – , / , * , .
    I want it should check 0-9 and return True else False

    Reply
    • Try this logic

      select case when col not like ‘%[^0-9]%’ then is_numeric else non_numeric end as col from your_table

      Reply
  • select case when col not like ‘%[^0-9]%’ then is_numeric else non_numeric end as col from your_table

    Reply
  • multiple value comman operator

    select *into(select *from split(tablecolumnname)as number table1)from table1

    Reply
  • Is there a way to find out the author of the user defined function?

    Reply
  • Hi All,
    I have posted one query lately but didn’t get any reply. So I am posting another post here.
    Can you post a query where we can find any object like either it’s a funcion or a view or any other object in a server having n number of databases.It should mention the datbase name,schema and also shows the the dependencies like if it is used in any stored procedure/view or in any sql jobs.

    Reply
  • THANKS
    ERCAN B.

    Reply
  • Hi,
    The above script gives function_name, schema name and type
    But, i need Parameters and Datatypes of the functions. Can you script it out please

    Thanks.

    Reply
    • You can use sys.all_parameters catalog view and put filter for function name

      select * from sys.all_parameters where object_id = object_id (‘AddNumbers’)

      Reply
  • we can user sp_helptext StoredprocedureName this command , shows the sp’s but
    how to view the user defined function in sqlserver

    Reply
  • Anand Chaudhary
    April 23, 2016 11:51 am

    HI All,

    Please help me , I want to make a user define functions in sql server but i don’t know how to make this.

    because there is a confusion in @parameter and define @variable why we use the this .

    supposed we are using parameter in function then why we need to use variable .

    if you will give me a relevant answer then it would be a big big help for me.

    Regards
    Anand Chaudhary

    Reply
  • Santosh Mohanty
    April 5, 2017 11:41 am

    How to know Alteration failure details on a table valued function using Query

    Reply
    • Santosh Mohanty
      April 5, 2017 11:48 am

      Hi All,

      Please help me out , suppose some one is Alter a table valued function but due to some region its not Command Complete successfully, but latter on i want know details on that
      1. What is error
      2. Date and Time of Execution

      Regard’s
      Santosh Mohanty

      Reply
  • Madhusudhan A
    May 25, 2017 10:17 am

    Hi Pinal Dave,

    How do we find out that a function is being used in the sql server.

    Thank You
    Madhusudhan A

    Reply
  • Hi, I want to get the list of tables used by a UDF in SQL server. Can anyone please help me with the query?

    Reply

Leave a Reply

Menu