Overview
This view displays the column information for tables and views of all tenants. It is consistent with CDB_TAB_COLS_V$, except that it lacks the NOTES column, which is currently unused.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | Tenant ID |
| OWNER | varchar(128) | NO | Owner of a table, view, or cluster |
| TABLE_NAME | varchar(128) | NO | The name of the table, view, or cluster. |
| COLUMN_NAME | varchar(128) | NO | Column name |
| DATA_TYPE | varchar(128) | NO | Data type of the column |
| DATA_TYPE_MOD | varchar(3) | NO | The default value is NULL |
| DATA_TYPE_OWNER | varchar(128) | NO | Default value: NULL |
| DATA_LENGTH | bigint(21) | NO | Column Length (in bytes) |
| DATA_PRECISION | bigint(20) | NO | NUMBER The decimal precision of the data type; FLOAT Binary precision of the data type; NULL for all other data types. |
| DATA_SCALE | bigint(20) | NO | The digits to the right of the decimal point in a number |
| NULLABLE | varchar(1) | NO | Indicates whether the column allows NULL values. This value is a column N Whether it exists. NOT NULL Whether the constraint or column belongs to PRIMARY KEY |
| COLUMN_ID | bigint(20) | NO | Sequence Number of the Created Column |
| DEFAULT_LENGTH | bigint(20) | NO | Length of the column's default value |
| DATA_DEFAULT | mediumtext | NO | Default value of the column |
| NUM_DISTINCT | bigint(20) | NO | Number of distinct values in the column |
| LOW_VALUE | varchar(128) | NO | Minimum value of the column |
| HIGH_VALUE | varchar(128) | NO | Maximum Value of Column |
| DENSITY | bigint(21) | NO | Column Density |
| NUM_NULLS | bigint(20) | NO | Number of NULLs in Column |
| NUM_BUCKETS | bigint(20) | NO | Number of Buckets in Column Histogram |
| LAST_ANALYZED | date | NO | Date of Last Analysis for This Column |
| SAMPLE_SIZE | bigint(20) | NO | The number of samples used to analyze this column. |
| CHARACTER_SET_NAME | varchar(44) | NO | Character set name:
|
| CHAR_COL_DECL_LENGTH | bigint(0) | NO | Default value: NULL |
| GLOBAL_STATS | varchar(3) | NO | GLOBAL_STATS If statistics are collected or incrementally maintained, they will YES Yes, otherwise it would be NO |
| USER_STATS | varchar(3) | NO | Indicates whether the statistics were directly entered by the user. YES) or not (NO) |
| AVG_COL_LEN | bigint(21) | NO | Average Column Length (Bytes) |
| CHAR_LENGTH | bigint(20) | NO | Displays the length of a column in characters. This value applies only to the following data types:
|
| CHAR_USED | varchar(1) | NO | Indicates that the column uses BYTE Length semantics (B) or CHAR Length semantics (C), or whether the data type is not any of the following (NULL):
|
| V80_FMT_IMAGE | varchar(3) | NO | Default value: NULL |
| DATA_UPGRADED | varchar(3) | NO | The default value is NULL |
| HIDDEN_COLUMN | varchar(3) | NO | Indicates whether the column is hidden. YES) or not (NO) |
| VIRTUAL_COLUMN | varchar(3) | NO | Indicates whether the column is a virtual column. YES) or not (NO) |
| SEGMENT_COLUMN_ID | bigint(0) | NO | Default value: NULL |
| INTERNAL_COLUMN_ID | bigint(0) | NO | The default value is NULL |
| HISTOGRAM | varchar(15) | NO | Indicates the presence or type of a histogram:
|
| QUALIFIED_COL_NAME | text | NO | Limited Column Name |
| USER_GENERATED | varchar(3) | NO | Indicates the column generation attribute. Valid values:
|
| DEFAULT_ON_NULL | varchar(3) | NO | Default value: NULL |
| IDENTITY_COLUMN | varchar(3) | NO | Default value: NULL |
| EVALUATION_EDITION | varchar(128) | NO | Default value: NULL |
| UNUSABLE_BEFORE | varchar(128) | NO | Default value: NULL |
| UNUSABLE_BEGINNING | varchar(128) | NO | Default value: NULL |
| COLLATION | varchar(100) | NO | Default value: NULL |
| COLLATED_COLUMN_ID | bigint(0) | NO | Default value: NULL |
Sample query
The system tenant queries the column information of table t1 under the tenant with ID 1002.
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_TAB_COLS WHERE CON_ID=1002 AND TABLE_NAME='t1'\G
The query result is as follows:
*************************** 1. row ***************************
CON_ID: 1002
OWNER: infotest
TABLE_NAME: t1
COLUMN_NAME: c1
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 0
DATA_PRECISION: 11
DATA_SCALE: 0
NULLABLE: Y
COLUMN_ID: 16
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: NULL
LOW_VALUE: NULL
HIGH_VALUE: NULL
DENSITY: NULL
NUM_NULLS: NULL
NUM_BUCKETS: NULL
LAST_ANALYZED: NULL
SAMPLE_SIZE: NULL
CHARACTER_SET_NAME:
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
AVG_COL_LEN: NULL
CHAR_LENGTH: 0
CHAR_USED: NULL
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: c1
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
*************************** 2. row ***************************
CON_ID: 1002
OWNER: infotest
TABLE_NAME: t1
COLUMN_NAME: c2
DATA_TYPE: NUMBER
DATA_TYPE_MOD: NULL
DATA_TYPE_OWNER: NULL
DATA_LENGTH: 0
DATA_PRECISION: 11
DATA_SCALE: 0
NULLABLE: Y
COLUMN_ID: 17
DEFAULT_LENGTH: NULL
DATA_DEFAULT: NULL
NUM_DISTINCT: NULL
LOW_VALUE: NULL
HIGH_VALUE: NULL
DENSITY: NULL
NUM_NULLS: NULL
NUM_BUCKETS: NULL
LAST_ANALYZED: NULL
SAMPLE_SIZE: NULL
CHARACTER_SET_NAME:
CHAR_COL_DECL_LENGTH: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
AVG_COL_LEN: NULL
CHAR_LENGTH: 0
CHAR_USED: NULL
V80_FMT_IMAGE: NULL
DATA_UPGRADED: NULL
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: NULL
INTERNAL_COLUMN_ID: NULL
HISTOGRAM: NULL
QUALIFIED_COL_NAME: c2
USER_GENERATED: YES
DEFAULT_ON_NULL: NULL
IDENTITY_COLUMN: NULL
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
COLLATION: NULL
COLLATED_COLUMN_ID: NULL
2 rows in set