41

I usually use SQLDeveloper to browse the database, but I couldn't make it work with HSQLDB and I don't know which tables are already created… I guess it's a vendor-specific question and not plain SQL, but the point is: how can I see the tables so I can drop/alter them?

Sae1962
  • 1,122
  • 15
  • 31
nobody
  • 413
  • 1
  • 4
  • 4

6 Answers6

50

The ANSI SQL92 standard for querying database metadata is contained within the INFORMATION_SCHEMA data structures.

I have no idea whether your database supports this or not, but try the following:

SELECT *
FROM   INFORMATION_SCHEMA.TABLES

On further research, it appears that HSQLDB does support INFORMATION_SCHEMA, but with slightly non-standard naming.

All of the tables have SYSTEM_* prepended to them, so the above example would read

SELECT *
FROM   INFORMATION_SCHEMA.SYSTEM_TABLES

I have no means of testing this, and the answer was found on sourceforge.

Sae1962
  • 1,122
  • 15
  • 31
Steve Weet
  • 28,126
  • 11
  • 70
  • 86
  • 2
    So I just need to add where TABLE_TYPE = 'TABLE' and I got it... thanks for your help! And the good news, according to the link you posted is that, for new versions, they will go follow the standard. Thanks again. – nobody Feb 26 '09 at 17:44
  • Table not found: TABLES in statement [SELECT * FROM INFORMATION_SCHEMA.TABLES] – cherouvim May 26 '09 at 08:57
  • 3
    HSQLDB 2.x supports both forms of SELECT given in the answer. – fredt Apr 14 '11 at 21:20
  • Dose the query syntax can execute in HSQL database manager? I enter the query syntax into it, but as cherouvim cited, Table not found:...Error Code -22/State:s0002. – parsifal Aug 02 '11 at 03:02
  • SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES; worked for me with a copy of the subsonic.org database. To get just the user tables add the where clause: SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE='TABLE'; – Lance Cleveland Nov 25 '11 at 17:11
  • with `hsqlDB` you can do a `CREATE TABLE IF NOT EXISTS newTable (column details...)` if you only wanted to be sure before creating a new one. – Chidi Sep 03 '19 at 13:59
14

Awesome, thanks! Been scouring the Web for that info. This will fetch only your tables' field info:

SELECT TABLE_NAME, COLUMN_NAME, TYPE_NAME, COLUMN_SIZE, DECIMAL_DIGITS, IS_NULLABLE FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME NOT LIKE 'SYSTEM_%'

You can retrieve indexes, primary key info, all kinds of stuff from INFORMATION_SCHEMA.SYSTEM_TABLES. Gotta love oo documentation :p

Sae1962
  • 1,122
  • 15
  • 31
  • there is no table in HSQLDB after executed using SELECT ... FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME NOT LIKE 'SYSTEM_%' in jdbc:hsqldb:mem: as URL connection. So it's connecting format is not for query, but it can connect in back end, right? – parsifal Aug 02 '11 at 03:08
  • That query works perfectly, saved me some time. I'm using HSQL 2.3.2 – cbaldan May 12 '16 at 13:56
4

If you're on the command line, you may want to try the Hsqldb SqlTool, documented in the SqlTool Manual (hsqldb.org).

  • Put your database connection information in "~/sqltool.rc" and choose any DBNAME you want, substitute correct username and password, if known.
    • urlid DBNAME
    • url jdbc:hsqldb:/path/to/hsql/database
    • username SA
    • password
  • Install tool with: apt-get install hsqldb-utils (on Ubuntu)
  • Connect with hsqldb-sqltool DBNAME # on Ubuntu
  • Hint for other systems: java -jar YourHsqlJar.jar DBNAME
  • Show tables with: \dt
  • Show columns with: \d TABLENAME
  • Standard queries like: SELECT * FROM …;
  • Edit (append) last command with: :a
  • Quit with: \q
  • View special commands with: \? OR :?

Good luck!

Mister_Tom
  • 1,500
  • 1
  • 23
  • 36
3

Use the \dt command when you hit the >sql prompt in the command line for HSQLDB.

Sae1962
  • 1,122
  • 15
  • 31
specialk1st
  • 1,717
  • 21
  • 20
1

Check out DBVisualiser and SQuirreL SQL Client. Both of these have support for HSQLDB, and a GUI for editing/modifying/viewing the tables.

Sae1962
  • 1,122
  • 15
  • 31
Anand
  • 7,654
  • 9
  • 46
  • 60
0

You run querying using hsql database manager, are you? If you use this, below may give some hints:

Select your connection:

  1. type: HSQL DATABASE ENGINE SERVER
  2. Driver: jdbc.hsqldb.jdbcDriver
  3. URL: jdbc:hsqldb:hsql://localhost/

Then, you will browse the database.

Sae1962
  • 1,122
  • 15
  • 31
parsifal
  • 879
  • 4
  • 12
  • 21