System tables of OmnisciDB for tables list
Hi, I am using DBeaver to connect to OmnisciDB, I don't see any system table where I can see the list of tables in a database. After research I found to query mapd_tables table but it says table not found.
Any suggestion for this is appreciated.
Hi Mohammad Sohail,
Well probably we should enhance out integration with dbeaver (I did something in the past, but I never published it), but since the 5.10 (in beta) and the 6.0 release we have a read-only database called information_schema with system tables
this is a list of 6.4 release:
To use it from dbeaver you have to create a new connection using information_schema as a database, or if you are using the 6.4 you can query those table from any database using information_schema. as a prefix of the table name.
You can query the tables as normal table e.g.
select database_name,owner_user_name,table_name from tables where table_name like '%flight%' or table_name like '%heavy%' limit 10;
You can also use aggregates or joins.
For example to know which are the 5 tables that are taking more space on your storage
select database_name,table_name,sum((total_data_file_size+total_dictionary_data_file_size)/1024/1024) from storage_details group by 1,2 order by 3 desc limit 5;
If you are on omnisci 5.9 or 5.10 make sure you tuyrn on the system tables using the parameter --enable-system-tables.
For a more detailed description of system tables please refer to our docs.
Please sign in to leave a comment.