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'}
                 test |           True | {'Cassandra': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
          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'}
            keyspace1 |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}
           solr_admin |           True |                        {'class': 'org.apache.cassandra.locator.EverywhereStrategy'}
            dse_audit |           True | {'Cassandra': '2', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
   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'}
  
  (15 rows)

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

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

  The following results shows the first record formatted with the cqlsh EXPAND ON option.

  @ Row 1
  -----------------------------+---------------------------------------------------------------------------------------------------------------------------
   keyspace_name               | cycling
   table_name                  | birthday_list
   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_threshold': '4'}
   compression                 | {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
   crc_check_chance            | 1
   default_time_to_live        | 0
   extensions                  | {'DSE_RLACA': 0x6379636c6973745f6e616d65}
   flags                       | {'compound'}
   gc_grace_seconds            | 864000
   id                          | e439b922-2bc5-11e8-891b-23da85222d3d
   max_index_interval          | 2048
   memtable_flush_period_in_ms | 0
   min_index_interval          | 128
   nodesync                    | null
   speculative_retry           | 99PERCENTILE
  ...

• 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 | type
  ---------------+--------------+-------------+------------------+----------------------+---------------+----------+------
         cycling | cyclist_name |   firstname |             none | 0x66697273746e616d65 |       regular |       -1 | text
         cycling | cyclist_name |          id |             none |               0x6964 | partition_key |        0 | uuid
         cycling | cyclist_name |    lastname |             none |   0x6c6173746e616d65 |       regular |       -1 | text
  
  (3 rows)

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