How to see all the tables in an HSQLDB database?2009-02-27 sql hsqldb
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?
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.
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
Gotta love oo documentation :p
You run querying using
hsql database manager, are you?
If you use this, below may give some hints:
Select your connection:
HSQL DATABASE ENGINE SERVER
Then, you will browse the database.
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.
- username SA
- 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:
- Show columns with: \d TABLENAME
- Standard queries like:
SELECT * FROM …;
- Edit (append) last command with:
- Quit with:
- View special commands with:
\dt command when you hit the
>sql prompt in the command line for HSQLDB.
- Find all tables containing column with specified name - MS SQL Server
- Get list of all tables in Oracle?
- How do I UPDATE from a SELECT in SQL Server?
- Add a column with a default value to an existing table in SQL Server
- How can I prevent SQL injection in PHP?
- Finding duplicate values in a SQL table
- How to list the tables in a SQLite database file that was opened with ATTACH?
- View content of H2 or HSQLDB in-memory database