Keyspace, table, and column information

Use CQL to query system_schema tables to view keyspace, table, and column information.

• Query the defined keyspaces using the SELECT statement.

  SELECT *
  FROM system_schema.keyspaces;

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

• Get the schema information for tables in the cycling keyspace.

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

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

  @ Row 1
  -----------------------------+-----------------------------------------------------------------------------------------------------------
  ----------------
   keyspace_name               | cycling
   table_name                  | cyclist_name
   bloom_filter_fp_chance      | 0.01
   caching                     | {'keys': 'ALL', 'rows_per_partition': 'NONE'}
   cdc                         | null
   comment                     | 
   compaction                  | {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_t
  hreshold': '4'}
   compression                 | {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
   crc_check_chance            | 1
   dclocal_read_repair_chance  | 0.1
   default_time_to_live        | 0
   extensions                  | {}
   flags                       | {'compound'}
   gc_grace_seconds            | 864000
   id                          | 8a1f5bf1-0258-11ea-bd8d-9f9b8a53b5f5
   max_index_interval          | 2048
   memtable_flush_period_in_ms | 0
   min_index_interval          | 128
   nodesync                    | null
   read_repair_chance          | 0
   speculative_retry           | 99PERCENTILE
  
  (1 rows)

• 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
  ---------------+--------------+-------------+------------------+----------------------+---------------+----------+-----------------------
  +------
         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)

  Note: The system_schema table does not show search index or row-level access control settings.