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

2 thoughts on “Monetdb : how to get schema and tables information ?

  1. Charles DiMaggio

    Thank you very much for this. Have been searching for something like this for quite some time. The MonetDB documentation leaves something to be desired in this regard. Your code worked perfectly.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *