38.5.18 #TABLES (displays table information)
- Function
-
This subcommand displays table information.
- Format
-
#TABLES [schema-name] [SHOW ALL];
- [schema-name]
-
Specifies a schema name for which table information is to be displayed.
If the schema name is omitted, table information for all tables is displayed. However, the tables whose information will be displayed vary depending on the privileges HADB users have. See Important points.
- [SHOW ALL]
-
If this option is specified
All columns in the SQL_TABLES dictionary table are displayed. For details about the displayed information, see Content of SQL_TABLES in the HADB Setup and Operation Guide.
When this option is omitted
Information shown in the following table is displayed.
Table 38‒7: Information displayed by #TABLES (if the option is omitted) No.
Column name
Description
1
TABLE_SCHEMA
Schema name
2
TABLE_NAME
Table identifier
3
STORAGE_FORMAT
Table-data storage format
-
'COLUMN'
Column store format
-
'ROW'
Row store format
The null value is displayed for a viewed table.
4
N_COLS
Number of columns in the table
5
N_INDEX
Number of indexes defined for the table (sum of all indexes)
6
IS_CHUNK
Specification of CHUNK chunk specification
-
Y: Specified (multi-chunk table)
-
N: Not specified (single chunk table)
7
N_CHUNK
- Number of existing chunks
-
If N is output for column name IS_CHUNK, a null value is displayed.
8
N_CHUNK_RESERVED
Maximum number of chunks to be created
9
CREATE_TIME
Time stamp when the table was defined
-
- Important points
-
-
The scope of table information displayed by executing #TABLES varies depending on the privileges HADB users have. The following describes the scope of table information to be displayed.
- When the authorization identifier of an HADB user with the DBA privilege or audit admin privilege is used to connect to the HADB server
-
-
Tables defined by all HADB users
-
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
-
-
Tables defined by this HADB user (with the authorization identifier that was used for the current connection to the HADB server)
-
Tables for which access privileges have been granted by other HADB users
-
Tables permitted in the PUBLIC specification
-
Tables whose schema name is MASTER
-
The scope of table information displayed by executing #TABLES is the same as the scope of information in the SQL_TABLES 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 #TABLES is executed, the dictionary table is locked. Execute the COMMIT statement immediately after you execute #TABLES. 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 #TABLES, the lock on the dictionary table is released, so you do not need to execute the COMMIT statement.
-
If the specified schema name does not exist, the execution result of #TABLES contains zero rows (no error is generated).
-
The execution results of #TABLES 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 #TABLES 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, or access path information is not output when #TABLES 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 #TABLES are output even if EXEC=PREPARE (do not execute the SQL statement) is specified in #SET OPT REPORT.
-