Query below return all stored procedures and information about them in Teradata database.

Query

SELECT  T.DatabaseName,
        T.TableName AS ProcedureName,
        CASE T.TableKind
            WHEN 'P' THEN 'Stored Procedure'
            WHEN 'E' THEN 'External Stored Procedure'
            end as ProcedureKind,
        T.CreateTimeStamp,
        TRIM(TRAILING ',' 
            FROM (XMLAGG(CASE SPParameterType
                        WHEN 'I' THEN 'IN '
                        WHEN 'O' THEN 'OUT '
                        WHEN 'B' THEN 'IN/OUT ' END
                    || C.ColumnName || ' '
                    || ColumnType || ','))(varchar(255))) as Parameters
FROM    DBC.TablesV T
LEFT JOIN   DBC.ColumnsV C
ON C.DatabaseName = T.DatabaseName
AND C.TableName = T.TableName
WHERE T.TableKind in ('P', 'E')
AND 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')
GROUP BY    T.DatabaseName,
            T.TableName,
            T.CreateTimeStamp,
            T.TableKind
ORDER BY    T.DatabaseName,
            ProcedureName;

Columns

• DatabaseName - name of the database
• ProcedureName - name of the procedure
• ProcedureKind
  • Stored Procedure
  • External Stored Procedure
• CreateTimeStamp - time when procedure was created
• Parameters - list of parameters with direction (IN, OUT, IN/OUT) and data type

Rows

• One row - represents one procedure
• Scope of rows: - all procedures in database
• Ordered by - procedure database and name

Sample results