Keyspace, table, and column information

Querying system_schema tables directly to get keyspace, table, and column information.

• Query the system_schema.keyspaces table using this 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'}
     
           
           
            keyspace1 |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}
           
            system_distributed |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
               system |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
            
        system_traces |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}
         
  
  (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
   dclocal_read_repair_chance  | 0.1
   default_time_to_live        | 0
    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
   read_repair_chance          | 0
   speculative_retry           | 99PERCENTILE
  ...

• Get details about a table's columns from the system_schema.columns table.

  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 tables do NOT show search index or row-level access control settings.