Select all UDFs from database?

Question

Something like:

SELECT * FROM sys.functions

Answer 1

Something like this will give you all the details of the udfs you've created.

SELECT *
    FROM
        sysobjects
    WHERE
        (type = 'TF' OR type = 'FN' OR type = 'IF')
        AND
        objectproperty(id, 'IsMSShipped') = 0

Get rid of the second condition if you want everything.

Answer 2

This will give you the names and the definitions :

SELECT SPECIFIC_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'FUNCTION'

Answer 3

for SQLServer2005 it is:

SELECT * 
    FROM sys.objects 
    WHERE type in ('TF','FN','IF')