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 cqlsh 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.

Procedure

Query the defined keyspaces using the SELECT statement.
```
SELECT * FROM system_schema.keyspaces;
```

 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)

 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;

The following results show the record for the cyclist_name table formatted with the cqlsh EXPAND ON option.

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.
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';

 keyspace_name | table_name | column_name | clustering_order | column_name_bytes | kind | position | required_for_liveness | type
---------------+------------+-------------+------------------+-------------------+------+----------+-----------------------+------

(0 rows)

 keyspace_name | table_name | column_name | clustering_order | column_name_bytes | kind | position | required_for_liveness | type
---------------+------------+-------------+------------------+-------------------+------+----------+-----------------------+------

(0 rows)

Keyspace, table, and column information

Procedure

Was this helpful?

Give Feedback