SQL query to get table information

Hi,

Want to check table info (table columns and column types) using SQL query. Tried describe tablename - didn’t work.

Any suggestion ?

Thanks!
Dipanwita

Hi @dipanwita2019,

Try to use the sql command

show create table *table name*;

You should get a create table statement with datatypes, encoding and constraints like not nullable fields

1 Like

Is there a way to query the system catalog? For example, if you want to get a list of tables in the database, or get a list of columns across tables filtered by a certain condition?

Or would you have to know the list of tables beforehand, run the show create table command on each, and then parse the results yourself to get the list of columns?

-Larry

Hi @Larry_Parker,

The system catalogs (there is one for each database) are SQLite’s databases that you can find into [data_dir]/mapd_catalog/[database_name]

Anyway they contains data that has to be decoded and it’s not exactly an easy task e.g.

candido@zion-legion:/mapd_storage/data_test/mapd_catalogs$ sqlite3 omnisci
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> select t.name,c.name,c.coltype,c.colsubtype from mapd_tables t join mapd_columns c on t.tableid=c.tableid where t.name like 'test%' and c.name like 'id';
test_sm|id|7|0
test_geom|id|6|0

as you can see the col type is a number that has to be decoded; in this case the 7 is a smallint and the 6 is an integer, the 13 is a text and so on.

I was playing with the catalog, extending actual SHOW commands with some filtering e.g.

omnisql> show tables like "test%" ;
table_name
test_timestamp
test_timestamp_32
test_double_float
test_array
testdatagrip
test_sm
test_cast_time_2
test_table
test_geom
9 rows returned.
Execution time: 1 ms, Total time: 1 ms

omnisql> show columns from table test_geom like "p%" ;
column_name|null|data_type|encoding|physical_size|logical_size
pol|YES|GEOMETRY(POLYGON, 4326)||-1|-1
poi|YES|GEOMETRY(POINT, 4326)||-1|-1
2 rows returned.
Execution time: 4 ms, Total time: 5 ms

BTW It’s now as powerful like a proper catalog, and I’m not sure is exactly what you need

1 Like

we can connect to omnisql and use \t to display tables
and \d “table name” to get details of table(don’t use ; at end of query)