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”:

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';

The resultset :

+------+---------------+----------+-------------+
| 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 |
+------+---------------+----------+-------------+