List stored procedure parameters in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    Query below return all parameters of stored routines (stored functions and stored procedures) and details about it in MySQL database.

    Query

    SELECT  T.DatabaseName,
            T.TableName AS ProcedureName,
            C.ColumnName AS ParameterName,
            CASE C.SPParameterType
                WHEN 'I' THEN 'IN'
                WHEN 'O' THEN 'OUT'
                WHEN 'B' THEN 'IN/OUT' END as ParameterDirection,
            C.ColumnType AS ParameterType
    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')
    ORDER BY    T.DatabaseName,
                ProcedureName;
    

    Columns

    • DatabaseName - name of the database
    • ProcedureName - name of the procedure
    • ParameterName - parameter name
    • ParameterDirection -parameter direction (IN, OUT, IN/OUT)
    • ParameterType - parameters data type. Some of possible values:
      • 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
      • All possible data types (look for Possible Values for ColumnType paragraph)

    Rows

    • One row - represents one procedure parameter
    • Scope of rows: - all parameters for procedures
    • Ordered by - database name, procedure name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept