33

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.

user1204320
  • 341
  • 1
  • 3
  • 8
  • 2
    When you receive a valid and correct answer would be good to mark it as accepted, that will help to others users and also to the people that spend time helping you to improve reputation. – Roberto Mar 21 '14 at 10:24

3 Answers3

72

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'

David Tonhofer
  • 14,559
  • 5
  • 55
  • 51
Roberto
  • 8,586
  • 3
  • 42
  • 53
4

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';
David Tonhofer
  • 14,559
  • 5
  • 55
  • 51
Millie Walsh
  • 179
  • 1
  • 6
4

The H2 database does not support the SQL statement DESC.

It does support SHOW however, as documented. Example:

SHOW TABLES
David Tonhofer
  • 14,559
  • 5
  • 55
  • 51
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132