B.3 Content of SQL_COLUMNS

SQL_COLUMNS stores column definition information of base tables, viewed tables, dictionary tables, dictionary tables (base tables), system tables, system tables (base tables). Each row stores information for one column.

The following table describes the content of SQL_COLUMNS.

Table B‒4: Content of SQL_COLUMNS
No.	Column name	Data type	Stored information
1	`TABLE_SCHEMA`	`VARCHAR(100)`	Schema name
2	`TABLE_NAME`	`VARCHAR(100)`	Table identifier
3	`COLUMN_NAME`	`VARCHAR(100)`	Column name
4	`COLUMN_ID`	`SMALLINT`	Column ID
5	`TABLE_ID`	`SMALLINT`	Table ID: From `0x00020001`: Dictionary tables (base tables) From `0x000200C9`: System tables (base tables) From `0x00020191`: Base tables defined by HADB users From `0x00040001`: Dictionary tables and system tables From `0x00040401`: Viewed tables defined by HADB users
6	`DATA_TYPE_CODE`	`SMALLINT`	Column data type: `113` (`0x71`): `DATE` `121` (`0x79`): `TIME` `125` (`0x7D`): `TIMESTAMP` `145` (`0x91`): `VARBINARY` `149` (`0x95`): `BINARY` `193` (`0xC1`): `VARCHAR` `197` (`0xC5`): `CHAR` `225` (`0xE1`): `DOUBLE` `PRECISION` `229` (`0xE5`): `DECIMAL` `241` (`0xF1`): `INTEGER` `245` (`0xF5`): `SMALLINT`
7	`DATA_LENGTH`	`SMALLINT`	Column definition length: When the column data type is one of those listed below Contains actual column definition length, in bytes. `INTEGER` `SMALLINT` `CHAR` `VARCHAR` `DATE` `DOUBLE` `PRECISION` `BINARY` `VARBINARY` When the column data type is TIME(`p`) Stores the value determined using the following formula: 3 + ↑`p` ÷ 2↑ `p`: 0, 3, 6, 9, or 12 When the column data type is TIMESTAMP(`p`) Stores the value determined using the following formula: 7 + ↑`p` ÷ 2↑ `p`: 0, 3, 6, 9, or 12 If the column data type is DECIMAL: The first byte stores the scaling and the second byte stores the precision. If the selection expression of a `CREATE` `VIEW` statement's query expression body includes a value expression for which the division result of the arithmetic operation is the `DECIMAL` data type, the following two values might not match: The scaling value stored here The scaling value of the derived column of the internal table derived from the equivalent exchange of a viewed table, when searching a viewed table. For details, see When searching a viewed table in Notes applying when the data type of the division result is DECIMAL in the manual HADB SQL Reference.
8	`IS_NULLABLE`	`CHAR(1)`	Whether the `NOT` `NULL` constraint was specified for the column: `'Y'` Null values are permitted (NOT NULL constraint was not specified) `'N'` Null values are not permitted (NOT NULL constraint was specified) In the case of a FIX table, all columns default to `'N'`.
9	`COLUMN_OFFSET`	`SMALLINT`	Column offset (a number indicating the position of the byte, counted from the beginning of the table, in which the column begins). The null value is stored for a table that is not a FIX table (table for which the `BRANCH` `ALL` option is not specified).
10	`BRANCH`	`CHAR(1)`	`BRANCH` specification value in column definition: `'Y'`: `YES` `'N'`: `NO` `'A'`: `AUTO` `'O'`: `VARCHAR`-type or `VARBINARY`-type column without `BRANCH` specified For columns whose type is not `VARCHAR` or `VARBINARY`, the null value is stored. The null value is also stored for columns in a column store table.
11	`DEFAULT_VALUE`	`VARCHAR(32000)`	Default value specified in the `DEFAULT` clause Stores the default value that was specified in the `DEFAULT` clause when a base table was defined. Literal Stores a literal if a literal was specified. For details, see Table B‒5: Value that is stored in the DEFAULT_VALUE column if a literal is specified. `CURRENT_DATE` Stores this value when `CURRENT_DATE` is specified. `CURRENT_TIME(p)` Stores this value when `CURRENT_TIME(p)` is specified. `p` shows this value in fractional seconds precision. When `p` = 0, `CURRENT_TIME` is stored. When `p` = 3, 6, 9, or 12, `CURRENT_TIME(p)` is stored. `CURRENT_TIMESTAMP(p)` Stores this value when `CURRENT_TIMESTAMP(p)` is specified. `p` shows this value in fractional seconds precision. When `p` = 0, `CURRENT_TIMESTAMP` is stored. When `p` = 3, 6, 9, or 12, `CURRENT_TIMESTAMP(p)` is stored. `CURRENT_USER` Stores this value when `CURRENT_USER` is specified. Null value Stores this value when `NULL` is specified. For columns of viewed tables, columns of dictionary tables (base tables), columns of system tables (base tables), and columns of base tables without the `DEFAULT` clause specified, the null value is stored.
12	`IS_DEFAULT_COLUMN`	`CHAR(1)`	Whether the `DEFAULT` clause is specified `'Y'` Column for which the `DEFAULT` clause was specified when a base table was defined Null value Column for which the `DEFAULT` clause was not specified when a base table was defined The null value is stored also for columns of viewed tables, columns of dictionary tables (base tables), and columns of system tables (base tables).
13	`IS_PRIMARY_KEY_COLUMN`	`CHAR(1)`	Whether a column comprises the primary key `'Y'` Column that comprises the primary key Null value Column that does not comprise the primary key The null value is stored also for columns of viewed tables, columns of dictionary tables (base tables), and columns of system tables (base tables).
14	`PRIMARY_KEY_COLUMN_SEQUENCE_NUMBER`	`SMALLINT`	Primary key configuration sequence Stores the sequence of columns comprising the primary key in ascending order, starting with `1`. The null value is stored for columns of viewed tables, columns of dictionary tables (base tables), columns of system tables (base tables), and columns of base tables that do not comprise the primary key.
15	`N_FOREIGN_KEY_COLUMN`	`SMALLINT`	Number of foreign keys that use this column Stores the number of foreign keys that use this column. The null value is stored for the following columns: Column of a viewed table Column of a dictionary table (base table) Column of a system table (base table) Column of a base table not comprising a foreign key
16	`IS_ARCHIVE_RANGE_COLUMN`	`CHAR(1)`	Is archive range column? `'Y'` Is archive range column Null value Is not archive range column The null value is stored for the following columns: Column of a viewed table Column of a dictionary table (base table) Column of a system table (base table)
17	`COMPRESSION_TYPE`	`VARCHAR(32)`	Value specified for `COMPRESSION TYPE` in column definition Stores the details specified for the compression-type specification when defining a column store table. The null value is stored for the following columns: Column of a viewed table Column of a dictionary table (base table) Column of a system table (base table) Column of a row store table

The following table describes the value that is stored in the DEFAULT_VALUE column when a literal is specified in the DEFAULT clause.

Table B‒5: Value that is stored in the DEFAULT_VALUE column if a literal is specified
No.	Data type assumed for the default value		Value that is stored in the DEFAULT_VALUE column
No.	Data type assumed for the default value		Data length (bytes)	Example of character format for the value that is stored
1	Numeric data	`INTEGER`	Length of the specified default value that has been converted to character format	`12345`
2		`SMALLINT`		`12345`
3		`DECIMAL`		`12.345`
4		`DOUBLE PRECISION`		`-1234567890.1234567E-123`
5	Character string data	`CHAR`	`Length of the specified default value that has been converted to character format` + 2	`'ABCD'`
6	Character string data	`VARCHAR`		`'ABCD'`
7	Datetime data	`DATE`	16	`DATE'YYYY-MM-DD'`
8		`TIME(p)`	If `p` is zero, 14 If `p` is not zero, 15 + `p`	`TIME'hh:mm:ss.nn...n'`^{#1, #2}
9		`TIMESTAMP(p)`	If `p` is zero, 30 If `p` is not zero, 31 + `p`	`TIMESTAMP'YYYY-MM-DD`Δ`hh:mm:ss.nn....n'`^#1,#2
10	Binary data	`BINARY`	Binary data in binary format, `Data length of the specified default value` × 8 + 3 Binary data in hexadecimal format, `Data length of the specified default value` × 2 + 3	Binary data in binary format `B'01010101'` Binary data in hexadecimal format `X'010203'`
11	Binary data	`VARBINARY`

Legend:

p: Fractional seconds precision (0, 3, 6, 9, or 12)

YYYY: Year

MM: Month

DD: Date

Δ: Space

hh: Hour

mm: Minute

ss: Second

nn...n: Fraction of a second

#1

If the fractional seconds precision of the column for which the DEFAULT clause is specified is zero, the period (.) between the second value (ss) and the fraction of a second (nn...n) is omitted.

#2

If the fractional seconds precision of the default value is shorter than the number of digits in the fraction of a second (nn...n) for the column for which the DEFAULT clause is specified, trailing zeros are padded to fill the length.

If the fractional seconds precision of the default value is greater than the number of digits in the fraction of a second (nn...n) for the column for which the DEFAULT clause is specified, the value is truncated.

To Page Top