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

« SQL SERVER – Find Great Job with Great Pay

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

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

February 2, 2008 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 10 Comments

10 Responses

on February 20, 2008 at 10:19 am | Reply Arthi

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.
on February 20, 2008 at 1:49 pm | Reply Madhivanan

Other method

select specific_name,specific_schema from information_schema.routines
where routine_type=’function’
on February 20, 2008 at 1:51 pm | Reply Madhivanan

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
on February 22, 2008 at 7:00 am | Reply SQL SERVER - Find All The User Defined Functions (UDF) - Part 2 Journey to SQL Authority with Pinal Dave

[...] 22, 2008 by pinaldave Few days ago, I wrote about SQL SERVER – Find All The User Defined Functions (UDF) in a Database. Regular reader of this blog Madhivanan has suggested following alternate method to do [...]
on October 14, 2009 at 12:25 am | Reply Hank

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?
on October 14, 2009 at 11:02 am | Reply Imran Mohammed

@Hank,

Follow this link for step by step procedure to use Database Publishing Wizard 2005/2008

http://products.secureserver.net/products/hosting/PublishingWithDPW.htm

Use this tool to script out all object, or specific object by owner, by type ….

Choose objects you want to script, tool will generate script.

Or
In Sql Server 2005
Right Click Database Name -> All tasks -> Generate script, Follow the wizard.

~ IM.
on December 3, 2009 at 5:40 pm | Reply Kalpesh

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’)
on April 21, 2011 at 12:54 am | Reply Tadeusz

@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’)
on October 13, 2011 at 10:50 pm | Reply Jasim Akhtar

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
- on October 14, 2011 at 6:19 pm | Reply madhivanan
  
  Try this logic
  
  select case when col not like ‘%[^0-9]%’ then is_numeric else non_numeric end as col from your_table