How to See All the Tables in an Hsqldb Database

How to see all the tables in an HSQLDB database?

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.

How can I see table structure in HSQLDB?

The information is provided by the views in the INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS

In version 2.x, additional views are available containing more detailed information:

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

You can select from single or joined views and filter the results on schema, table, column names and table type. The last you can use to show non-system tables only.

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

How to get all the tables which I created using hsqldb runserver in standlone mode

You seem to be trying to access the database simultaneously via Server and in-process. This is not possible. After each type of access, you need to shutdown the database before you connect to it via the alternative type of access. Therefore shutdown the Server once you have created the tables, then connect to the database in-process.

It is easier to access the database via Server only, and avoid such problems. Please also upgrade to version 2.2.8 to avoid a bug which may affect a database that has not been shutdown properly.

HSQLDB list all constraints

HSQLDB system tables are listed in the Guide

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

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS view has the overall information. Other tables list the columns used in constraints.

HSQLDB equivalent to Use databasename ;

The database is selected as part of the JDBC URL. You can not change that at runtime without creating a new connection.

But you are mixing up terminology anyway

SELECT * FROM TESTDB.NAMES 

selects from a schema called testdb not from a "database" (MySQL doesn't distinguish between a database and a schema, that's probably why you mixed up the terminology as well)

The SQL standard defines two namespace levels: catalog and schema.

A fully qualified name is database_name.schema_name.object_name. You can only leave out parts from the beginning. So foo.bar references a table bar in the schema foo. As HSQLDB does not support more than one catalog per database (or actually: the current database is the "catalog") you can always leave out the catalog name when referencing a table.

See the manual for details:

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

To avoid having to specify the table's schema, you can change the current schema using the set schema statement:

SET SCHEMA testdb;

This is also documented in the manual:

http://hsqldb.org/doc/2.0/guide/sessions-chapt.html#N10BBF

---

Related Topics