Keyspace, table, and column information

Querying system.schema_* tables directly to get keyspace, table, and column information.

An alternative to the cqlsh describe_* functions or using DataStax DevCenter to discover keyspace, table, and column information is querying system.schema_* table directly.

Procedure

  • 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 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
     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
     read_repair_chance          | 0
     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.