Keyspace, table, and column information

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

An alternative to the cqlsh DESCRIBE command or using DataStax Studio 2.0 to discover keyspace, table, and column information is to query the system_schema tables directly.

The examples in this section show how to query the system_schema.keyspaces table and additional tables in the system_schema keyspace.
Note: The system.schema_keyspaces table no longer exists in Datastax Enterprise 5.0 and higher, and is replaced by the system_schema.keyspaces table.

Procedure

  • 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'}
       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
     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 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.