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?
A:
I suppose you can query some system table to get the tables metadata. Good luck!
Rafa G. Argente
2009-02-26 17:24:23
Indeed. In MySQL, there is the database "information_schema", which contains a table called "TABLES", which can be inspected to know about existing tables in every database.
João da Silva
2009-02-26 17:25:51
It doesn't work in Hsqldb... I couldn't find the system table names.
nobody
2009-02-26 17:30:32
+4
A:
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
Steve Weet
2009-02-26 17:27:22
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
2009-02-26 17:44:39
Table not found: TABLES in statement [SELECT * FROM INFORMATION_SCHEMA.TABLES]
cherouvim
2009-05-26 08:57:34
A:
Check out DBVisualiser and SQuirreL SQL Client. Both of these have support for hsqldb, and a GUI for editing/viewing/modifying the tables.
trex279
2009-02-26 17:43:34
A:
Do you have installed SQLDeveloper hsqldb driver?
Is it installed correctly?
Max Gontar
2009-02-26 17:48:39
A:
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 kindza stuff from INFORMATION_SCHEMA.SYSTEM_TABLES. Gotta love oo documentation :p