CODEWITHSUNDEEP
h2
user1204320
user1204320

Reputation: 351

How to use DESC command in H2 Database?

My friend showed me in ORACLE that using DESC Table NAme was showing information about columns of table. But running this command in H2 Database was giving error, Why? and please tell how to use this command in H2? Thanks.

Upvotes: 34

Views: 47727

Answers (3)

Roberto
Roberto

Reputation: 9100

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'

Upvotes: 80

Millie Walsh
Millie Walsh

Reputation: 179

If you are looking for more information about tables in the "Information Schema" see:

http://www.h2database.com/html/systemtables.html#information_schema

for example:

select * from information_schema.indexes where table_name = 'table_name';

Upvotes: 4

Thomas Mueller
Thomas Mueller

Reputation: 50147

The H2 database does not support the SQL statement DESC.

It does support SHOW however, as documented. Example:

SHOW TABLES

Upvotes: 3

Related Questions