Keyspace, table, and column information

Use CQL to query system_schema tables to view keyspace, table, and column information. The same information is also available using the CQL shell DESCRIBE commands, which is the preferred method. The system_schema table does not show search index or row-level access control settings.

To secure schema data, enable system_keyspaces_filtering. Users can only access schema information of objects for which they have permission to access.

Query the defined keyspaces using the SELECT statement.

SELECT * FROM system_schema.keyspaces;

The output depends on your cluster and user-created keyspaces. For example:

Result

 keyspace_name      | durable_writes | graph_engine | replication
--------------------+----------------+--------------+-------------------------------------------------------------------------------------
            cycling |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
        system_auth |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
      system_schema |           True |         null |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
   dse_system_local |           True |         null |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
         dse_system |           True |         null |                        {'class': 'org.apache.cassandra.locator.EverywhereStrategy'}
         dse_leases |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
        cycling_alt |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
         solr_admin |           True |         null |                        {'class': 'org.apache.cassandra.locator.EverywhereStrategy'}
       dse_insights |           True |         null |                        {'class': 'org.apache.cassandra.locator.EverywhereStrategy'}
 dse_insights_local |           True |         null |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
 system_distributed |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
     system_backups |           True |         null |                        {'class': 'org.apache.cassandra.locator.EverywhereStrategy'}
             system |           True |         null |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
           dse_perf |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
      system_traces |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}
       dse_security |           True |         null | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}

(16 rows)

Get the schema information for tables in the cycling keyspace.

EXPAND ON;
SELECT * FROM system_schema.tables
  WHERE keyspace_name = 'cycling' AND table_name = 'cyclist_name';
EXPAND OFF;

You can format the output with the CQL shell ON option.

Result

Now Expanded output is enabled

 keyspace_name | table_name | additional_write_policy | bloom_filter_fp_chance | caching | cdc | comment | compaction | compression | crc_check_chance | dclocal_read_repair_chance | default_time_to_live | extensions | flags | gc_grace_seconds | id | max_index_interval | memtable_flush_period_in_ms | min_index_interval | nodesync | read_repair | read_repair_chance | speculative_retry
---------------+------------+-------------------------+------------------------+---------+-----+---------+------------+-------------+------------------+----------------------------+----------------------+------------+-------+------------------+----+--------------------+-----------------------------+--------------------+----------+-------------+--------------------+-------------------


(0 rows)
Disabled Expanded output.

Get details about a table’s columns from system_schema.columns.

SELECT * FROM system_schema.columns
  WHERE keyspace_name = 'cycling' AND table_name = 'cyclist_name';

Result

 keyspace_name | table_name   | column_name | clustering_order | column_name_bytes    | kind          | position | required_for_liveness
| type
---------------+--------------+-------------+------------------+----------------------+---------------+----------+-----------------------
+------
       cycling | cyclist_name |     comment |             none |     0x636f6d6d656e74 |       regular |       -1 |                 False
| text
       cycling | cyclist_name |   firstname |             none | 0x66697273746e616d65 |       regular |       -1 |                 False
| text
       cycling | cyclist_name |          id |             none |               0x6964 | partition_key |        0 |                 False
| uuid
       cycling | cyclist_name |    lastname |             none |   0x6c6173746e616d65 |       regular |       -1 |                 False
| text

(4 rows)

Keyspace, table, and column information

Was this helpful?

Give Feedback