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 (http://blog.SQLAuthority.com)

17 thoughts on “SQL SERVER – Find All The User Defined Functions (UDF) in a Database

  1. 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.

    Like

  2. Pingback: SQL SERVER - Find All The User Defined Functions (UDF) - Part 2 Journey to SQL Authority with Pinal Dave

  3. 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’)

    Like

  4. @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’)

    Like

  5. 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

    Like

  6. 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.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s