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.

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)

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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com