Query below return all User Defined Functions and information about it in Teradata database.

Query

SELECT  C.DatabaseName,
        FunctionName,
        NumParameters,
        ParameterDataTypes,
        CASE SrcFileLanguage
            WHEN 'S' THEN 'SQL'
            WHEN 'C' THEN 'C'
            WHEN 'P' THEN 'C++'
            WHEN 'J' THEN 'JAVA'
            WHEN 'A' THEN 'SAS'
            END AS FunctionLanguage,
        CASE FunctionType
            WHEN 'A' THEN 'Aggregate'
            WHEN 'B' THEN 'Aggregate and statistical'
            WHEN 'C' THEN 'Contract function'
            WHEN 'F' THEN 'Scalar'
            WHEN 'H' THEN 'User-defined method'
            WHEN 'I' THEN 'Internal type method'
            WHEN 'L' THEN 'Table operator'
            WHEN 'R' THEN 'Table'
            WHEN 'S' THEN 'Statistical'
            END,
        ColumnType AS ReturnType
FROM    DBC.FunctionsV T
LEFT JOIN   DBC.ColumnsV C
ON  C.DatabaseName = T.DatabaseName
AND C.TableName = T.SpecificName
AND ColumnName = 'RETURN0'
WHERE   T.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
ORDER BY    C.DatabaseName,
            FunctionName;

Columns

• DatabaseName - database name
• FunctionName - function name
• NumParameters - number of function arguments
• ParameterDataTypes - each two character represents data type of argument. Some of possible types:
  • CF - Char
  • CV - Varchar
  • D - Decimal
  • DA - Date
  • F - Float
  • I2 - Small Int
  • I - Integer
  • AT - Time
  • TZ - Time with time zone
  • TS - Timestamp
  • SZ - Timestamp with time zone
  • BO - BLOB
  • CO - CLOB
  • All possible data types (look for Possible Values for ColumnType paragraph)
• FunctionLanguage - language in which function is written
• FunctionType - function type
• ReturnType - data type of returned value

Rows

• One row - represents one function
• Scope of rows: - all functions in database
• Ordered by - database name, function name

Sample results