TABLE_COLUMNS scalar function

Returns a string containing the comma-separated list of columns of the specified table in the order they are defined

Prototypes

TABLE_COLUMNS(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
TABLE_COLUMNS(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
TABLE_COLUMNS(ATABLE VARCHAR(128))

Description

This function returns a string containing a comma-separated list of the columns in the specified table in the order that they exist in the table.

If ASCHEMA is omitted it defaults to the value of the CURRENT SCHEMA special register. If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns will be included, otherwise they are excluded. GENERATED BY DEFAULT columns are always included. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns will be included, otherwise they are excluded.

Parameters

ASCHEMA
If provided, the schema containing the table for which to return a column list. Defaults to the value of the CURRENT SCHEMA special register if omitted.
ATABLE
The table for which to return a column list.
INCLUDE_GENERATED
If provided, specifies whether to include GENERATED ALWAYS columns in the result. Defaults to 'Y' if omitted.
INCLUDE_IDENTITY
If provided, specifies whether to include IDENTITY columns in the result. Defaults to 'Y' if omitted.

Examples

Return a comma-separated list of the columns in the SYSIBM.SYSTABLES table:

VALUES TABLE_COLUMNS('SYSIBM', 'SYSTABLES', 'Y', 'Y');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NAME,CREATOR,TYPE,CTIME,REMARKS,PACKED_DESC,VIEW_DESC,COLCOUNT,FID,TID,CARD,NPAGES,FPAGES,OVERFLOW,PARENTS,CHILDREN,SELFREFS,KEYCOLUMNS,KEYOBID,REL_DESC,BASE_NAME,BASE_SCHEMA,TBSPACE,INDEX_TBSPACE,LONG_TBSPACE,KEYUNIQUE,CHECKCOUNT,CHECK_DESC,STATS_TIME,DEFINER,TRIG_DESC,DATA_CAPTURE,STATUS,CONST_CHECKED,PMAP_ID,ENCODING_SCHEME,PCTFREE,ROWTYPESCHEMA,ROWTYPENAME,APPEND_MODE,PARTITION_MODE,REFRESH,REFRESH_TIME,LOCKSIZE,VOLATILE,REMOTE_DESC,CLUSTERED,AST_DESC,DROPRULE,LOGINDEXBUILD,PROPERTY,STATISTICS_PROFILE,COMPRESSION,ACCESS_MODE,ACTIVE_BLOCKS,MAXFREESPACESEARCH,AVGCOMPRESSEDROWSIZE,AVGROWCOMPRESSIONRATIO,AVGROWSIZE,PCTROWSCOMPRESSED,CODEPAGE,PCTPAGESSAVED,LAST_REGEN_TIME,SECPOLICYID,PROTECTIONGRANULARITY,INVALIDATE_TIME,DEFINERTYPE,ALTER_TIME,AUDITPOLICYID,COLLATIONID,COLLATIONID_ORDERBY,ONCOMMIT,ONROLLBACK,LOGGED,LASTUSED

See the implementation of EXPORT_TABLE scalar function for an example of the usage of this function within a stored procedure.