38.5.2 #COLUMNS (displays column information)
- Function
-
This subcommand displays column information for the specified table.
- Format
-
#COLUMNS [schema-name.]table-identifier [SHOW ALL];
- [schema-name.]table-identifier
-
Specifies the name of a table for which column information is to be displayed, in the format schema-name.table-identifier. If the schema name is omitted, the authorization identifier of the HADB user who executed this subcommand is assumed.
For details about the specification rules that apply when a schema name or table identifier includes any lowercase letter or backslash (\), see 1.4.3 Table name specification rules.
- [SHOW ALL]
-
If this option is specified
All columns in the SQL_COLUMNS dictionary table are displayed. For details about the displayed information, see Content of SQL_COLUMNS in the HADB Setup and Operation Guide.
When this option is omitted
Information shown in the following table is displayed.
Table 38‒4: Information displayed by #COLUMNS (if the option is omitted) No.
Column name
Description
1
COLUMN_NAME
Column name
2
DATA_TYPE
Data type
3
DATA_LENGTH
- Column definition length
-
For details, see DATA_LENGTH in Content of SQL_COLUMNS in the HADB Setup and Operation Guide.
4
IS_NULLABLE
- Whether the NOT NULL constraint is specified for the column
-
For details, see IS_NULLABLE in Content of SQL_COLUMNS in the HADB Setup and Operation Guide.
5
IS_DEFAULT_COLUMN
Whether the DEFAULT clause is specified
-
Y
Column for which the DEFAULT clause was specified when a base table was defined
-
N
Column for which the DEFAULT clause is not specified when a base table is defined
- Important points
-
-
The scope of column information in tables displayed by executing #COLUMNS varies depending on the privileges HADB users have. The following describes the scope of column information in tables to be displayed.
- When the authorization identifier of an HADB user with the DBA privilege is used to connect to the HADB server
-
-
Columns in tables defined by all HADB users
-
Columns in tables whose schema name is MASTER
-
- When the authorization identifier of an HADB user without the preceding privilege is used to connect to the HADB server
-
-
Columns in the tables defined by this HADB user (with the authorization identifier that was used for the current connection to the HADB server)
-
Columns in tables for which access privileges have been granted by other HADB users
-
Columns in tables permitted in the PUBLIC specification
-
Columns in tables whose schema name is MASTER
-
The scope of column information in tables displayed by executing #COLUMNS is the same as the scope of information in the SQL_COLUMNS dictionary table that can be referenced. For details, see Scope of information in dictionary tables that can be referenced by HADB users in Dictionary table overview in the HADB Setup and Operation Guide.
-
When #COLUMNS is executed, the dictionary table is locked. Execute the COMMIT statement immediately after you execute #COLUMNS. If you do not execute the COMMIT statement, the lock on the dictionary table will not be released. Note that when you terminate the adbsql command after executing #COLUMNS, the lock on the dictionary table is released, so you do not need to execute the COMMIT statement.
-
If the specified schema name or table identifier does not exist, the execution result of #COLUMNS contains zero rows (no error is generated).
-
The execution results of #COLUMNS are output even if OFF (do not output search results) is specified in #SET DISPLAY. For details about #SET DISPLAY of the adbsql subcommand, see 38.5.12 #SET DISPLAY (outputs the retrieval results).
-
The #COLUMNS execution time is not output even if ON (output SQL statement execution time) is specified in #SET EXECTIME REPORT. For details about #SET EXECTIME REPORT of the adbsql subcommand, see 38.5.14 #SET EXECTIME REPORT (outputs SQL statement execution time).
-
Even if ON (display index, work table, and access path information) is specified in #SET OPT REPORT, index information, work table information, and access path information are not output when #COLUMNS is executed. For details about #SET OPT REPORT of the adbsql subcommand, see 38.5.16 #SET OPT REPORT (displays index, work table, and access path information).
-
The execution results of #COLUMNS are output even if EXEC=PREPARE (do not execute the SQL statement) is specified in #SET OPT REPORT.
-