Monthly Archives: June 2016

Monetdb : how to get schema and tables information ?

As other SQL database engines, monetdb allows to query schema and table information through SQL queries over a few system tables, namely : sys.schemas, sys.tables, and sys.columns.

The following query will give columns details for the system table “tables”:
[code lang=sql]
SELECT c.id, c.name, c.type, c.type_digits
FROM sys.columns c LEFT JOIN sys.tables t ON c.table_id = t.id
WHERE t.name = ‘tables’;
[/code]

The resultset :
[code]
+——+—————+———-+————-+
| id | name | type | type_digits |
+======+===============+==========+=============+
| 5211 | id | int | 32 |
| 5212 | name | varchar | 1024 |
| 5213 | schema_id | int | 32 |
| 5214 | query | varchar | 2048 |
| 5215 | type | smallint | 16 |
| 5216 | system | boolean | 1 |
| 5217 | commit_action | smallint | 16 |
| 5218 | access | smallint | 16 |
| 5219 | temporary | smallint | 16 |
+——+—————+———-+————-+
[/code]