It is very important to note, that, the information below, is for database back-end,
HSQL, Version
1.8.0 . . . and . . . the INFORMATION_SCHEMA layout is different for
HSQL Version 2.0 and afterwards.
Code: Select all
TABLE_SCHEM TABLE_NAME
------------------ -------------------------------------
INFORMATION_SCHEMA SYSTEM_ALIASES
INFORMATION_SCHEMA SYSTEM_ALLTYPEINFO
INFORMATION_SCHEMA SYSTEM_AUTHORIZATIONS
INFORMATION_SCHEMA SYSTEM_BESTROWIDENTIFIER
INFORMATION_SCHEMA SYSTEM_CACHEINFO
INFORMATION_SCHEMA SYSTEM_CATALOGS
INFORMATION_SCHEMA SYSTEM_CHECK_COLUMN_USAGE
INFORMATION_SCHEMA SYSTEM_CHECK_CONSTRAINTS
INFORMATION_SCHEMA SYSTEM_CHECK_ROUTINE_USAGE
INFORMATION_SCHEMA SYSTEM_CHECK_TABLE_USAGE
INFORMATION_SCHEMA SYSTEM_CLASSPRIVILEGES
INFORMATION_SCHEMA SYSTEM_COLLATIONS
INFORMATION_SCHEMA SYSTEM_COLUMNPRIVILEGES
INFORMATION_SCHEMA SYSTEM_COLUMNS
INFORMATION_SCHEMA SYSTEM_CROSSREFERENCE
INFORMATION_SCHEMA SYSTEM_INDEXINFO
INFORMATION_SCHEMA SYSTEM_PRIMARYKEYS
INFORMATION_SCHEMA SYSTEM_PROCEDURECOLUMNS
INFORMATION_SCHEMA SYSTEM_PROCEDURES
INFORMATION_SCHEMA SYSTEM_PROPERTIES
INFORMATION_SCHEMA SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS
INFORMATION_SCHEMA SYSTEM_SCHEMAS
INFORMATION_SCHEMA SYSTEM_SCHEMATA
INFORMATION_SCHEMA SYSTEM_SEQUENCES
INFORMATION_SCHEMA SYSTEM_SESSIONINFO
INFORMATION_SCHEMA SYSTEM_SESSIONS
INFORMATION_SCHEMA SYSTEM_SUPERTABLES
INFORMATION_SCHEMA SYSTEM_SUPERTYPES
INFORMATION_SCHEMA SYSTEM_TABLE_CONSTRAINTS
INFORMATION_SCHEMA SYSTEM_TABLEPRIVILEGES
INFORMATION_SCHEMA SYSTEM_TABLES
INFORMATION_SCHEMA SYSTEM_TABLETYPES
INFORMATION_SCHEMA SYSTEM_TEXTTABLES
INFORMATION_SCHEMA SYSTEM_TRIGGERCOLUMNS
INFORMATION_SCHEMA SYSTEM_TRIGGERS
INFORMATION_SCHEMA SYSTEM_TYPEINFO
INFORMATION_SCHEMA SYSTEM_UDTATTRIBUTES
INFORMATION_SCHEMA SYSTEM_UDTS
INFORMATION_SCHEMA SYSTEM_USAGE_PRIVILEGES
INFORMATION_SCHEMA SYSTEM_USERS
INFORMATION_SCHEMA SYSTEM_VERSIONCOLUMNS
INFORMATION_SCHEMA SYSTEM_VIEW_COLUMN_USAGE
INFORMATION_SCHEMA SYSTEM_VIEW_ROUTINE_USAGE
INFORMATION_SCHEMA SYSTEM_VIEW_TABLE_USAGE
INFORMATION_SCHEMA SYSTEM_VIEWS
For example, the content of
HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_COLUMNS is:
Code: Select all
NAME DATATYPE WIDTH NO-NULLS PRECISION SCALE
----------------- -------- ----- -------- ---------- -----
TABLE_CAT VARCHAR 32766 2147483647
TABLE_SCHEM VARCHAR 32766 2147483647
TABLE_NAME VARCHAR 32766 * 2147483647
COLUMN_NAME VARCHAR 32766 * 2147483647
DATA_TYPE SMALLINT 6 * 5
TYPE_NAME VARCHAR 32 * 2147483647
COLUMN_SIZE INTEGER 11 10
BUFFER_LENGTH INTEGER 11 10
DECIMAL_DIGITS INTEGER 11 10
NUM_PREC_RADIX INTEGER 11 10
NULLABLE INTEGER 11 * 10
REMARKS VARCHAR 32766 2147483647
COLUMN_DEF VARCHAR 32766 2147483647
SQL_DATA_TYPE INTEGER 11 10
SQL_DATETIME_SUB INTEGER 11 10
CHAR_OCTET_LENGTH INTEGER 11 10
ORDINAL_POSITION INTEGER 11 * 10
IS_NULLABLE VARCHAR 3 * 2147483647
SCOPE_CATLOG VARCHAR 32766 2147483647
SCOPE_SCHEMA VARCHAR 32766 2147483647
SCOPE_TABLE VARCHAR 32766 2147483647
SOURCE_DATA_TYPE VARCHAR 32766 2147483647
TYPE_SUB INTEGER 11 * 10
and the content of
HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_TABLES is:
Code: Select all
NAME DATATYPE WIDTH NO-NULLS PRECISION SCALE
------------------------- -------- ----- -------- ---------- -----
TABLE_CAT VARCHAR 32766 2147483647
TABLE_SCHEM VARCHAR 32766 2147483647
TABLE_NAME VARCHAR 32766 * 2147483647
TABLE_TYPE VARCHAR 16 * 2147483647
REMARKS VARCHAR 32766 2147483647
TYPE_CAT VARCHAR 32766 2147483647
TYPE_SCHEM VARCHAR 32766 2147483647
TYPE_NAME VARCHAR 32766 2147483647
SELF_REFERENCING_COL_NAME VARCHAR 32766 2147483647
REF_GENERATION VARCHAR 32766 2147483647
HSQLDB_TYPE VARCHAR 6 2147483647
READ_ONLY BOOLEAN 5 * 1
Therefore, using the following Query (
HSQL 1.8.0 and run in
DIRECT MODE ), will return a list of your table / view names, in the assigned sort order:
Code: Select all
Select TABLE_TYPE, TABLE_NAME From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = 'PUBLIC' ORDER BY TABLE_TYPE, UPPER(TABLE_NAME)
and, the following Query(
HSQL 1.8.0 and run in
DIRECT MODE ), will return a list of table only names, and, some column information:
Code: Select all
Select T.TABLE_TYPE, T.TABLE_NAME, C.ORDINAL_POSITION, C.COLUMN_NAME, C.TYPE_NAME, C.COLUMN_SIZE From INFORMATION_SCHEMA.SYSTEM_TABLES AS T, INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where T.TABLE_SCHEM = 'PUBLIC' and T.TABLE_NAME = C.TABLE_NAME and T.TABLE_TYPE = 'TABLE' ORDER BY T.TABLE_TYPE, UPPER(T.TABLE_NAME), C.ORDINAL_POSITION
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.