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]
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.
Thanks for the comment, I’m glad this was useful to you.
Nicolas