CODEWITHSUNDEEP
sqlhsqldb
C. E.
C. E.

Reputation: 10627

Exploring HSQL database using SQL queries?

I'm using HSQL for the first time. I've previously used MySQL. It appears to lack MySQL commands such as SHOW TABLES. I will still need get information like that occasionally though. This is somewhat similar, but it returns more tables than I care for (not all my own):

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE='TABLE'

So for that reason I would like to explore the meta tables being used by HSQL, but I don't know how. Could someone run me through the basics of how to explore an HSQL database to find out information about the overall structure. For example

  1. How would I find out the column names of INFORMATION_SCHEMA.SYSTEM_TABLES?
  2. How could I even have found INFORMATION_SCHEMA in the first place?
  3. How do I find "siblings" to SYSTEM_TABLES?
  4. How to get details about a column, like primary key and such?

etc.

Upvotes: 0

Views: 745

Answers (1)

fredt
fredt

Reputation: 24382

Some of this information is covered in the HSQLDB Guide:

http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_standard_views_info_schema

The Standard view equivalent is INFORMATION_SCHEMA.TABLES and you can filter out the system views by adding WHERE TABLE_SCHEMA <> 'INFORMATION_SCHEMA' or a similar condition.

The column details are in INFORMATION_SCHEMA.COLUMNS, while primary keys are listed in KEY_COLUMN_USAGE.

Upvotes: 2

Related Questions