How to use DESC command in H2 Database?

Question

you can use the SHOW command just like:

sql> show columns from users;

“users” is the table name, the output would be something like:

FIELD      | TYPE          | NULL | KEY | DEFAULT
ID         | INTEGER(10)   | NO   | PRI | (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_B66F0B87_5AAA_4421_88AC_1E8CAC372596)
USERNAME   | VARCHAR(45)   | NO   |     | NULL
PASSWORD   | VARCHAR(100)  | YES  |     | NULL
FULL_NAME  | VARCHAR(100)  | YES  |     | NULL
LAST_LOGIN | TIMESTAMP(23) | YES  |     | NULL
(5 rows, 1 ms)

Grammar diagram from the manual:

Syntax diagram for SHOW COLUMNS from the manual

The color red, according to the manual, means:

Compatibility-only non-standard syntax is marked in red, don’t use it unless you need it for compatibility with other databases or old versions of H2.

(Apparently getting table metadata has not been standardized by ANSI?)

The alternative is to query the table INFORMATION_SCHEMA.COLUMNS.

As an example, a self-referential query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' AND TABLE_NAME = 'COLUMNS'

Leave a Comment Cancel reply