Keyspace, table, and column information

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

Use CQL to query system_schema tables to view keyspace, table, and column information. The same information is also available using the cqlsh DESCRIBE commands, which is the preferred method.

Tip: To secure schema data, enable system_keyspaces_filtering. Users can only access schema information of objects for which they have permission to access.

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