2

I can use show tables in <database name> to show all tables in a database. The results returned show if a table has clustering enabled - shows the cluster_by column. Is there a way to get back a list of all tables that have value in cluster_by ?

The documentation for show-tables shows only:

SHOW [ TERSE ] TABLES [ HISTORY ] [ LIKE '<pattern>' ]
                                  [ IN { ACCOUNT | DATABASE [ <db_name> ] | SCHEMA [ <schema_name> ] } ]
                                  [ STARTS WITH '<name_string>' ]
                                  [ LIMIT <rows> [ FROM '<name_string>' ] ]
AlexD
  • 4,062
  • 5
  • 38
  • 65

1 Answers1

5

You can always ask INFORMATION_SCHEMA:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CLUSTERING_KEY 
  FROM INFORMATION_SCHEMA.TABLES
 WHERE CLUSTERING_KEY IS NOT NULL;

or using RESULT_SCAN

SHOW TABLES IN DATABASE TEST;

SELECT * 
FROM TABLE(result_scan(last_query_id())) 
WHERE "cluster_by" <> '';

Reference: INFORMATION SCHEMA TABLES VIEW, RESULT_SCAN

Michael Golos
  • 1,814
  • 1
  • 6
  • 16