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. The system_schema table does not show search index or row-level access control settings.

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.NetworkTopologyStrategy', 'dc1': '1'}
        system_auth |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
      system_schema |           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'}
        cycling_alt |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
       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'}

(13 rows)
  • Get the schema information for tables in the cycling keyspace.

    EXPAND ON;
    SELECT * FROM system_schema.tables 
      WHERE keyspace_name = 'cycling' AND table_name = 'cyclist_name';
    EXPAND OFF;

The following results show the record for the cyclist_name table formatted with the cqlsh EXPAND ON option.

Now Expanded output is enabled

 keyspace_name | table_name | bloom_filter_fp_chance | caching | cdc | comment | compaction | compression | crc_check_chance | dclocal_read_repair_chance | default_time_to_live | extensions | flags | gc_grace_seconds | id | max_index_interval | memtable_flush_period_in_ms | min_index_interval | read_repair_chance | speculative_retry
---------------+------------+------------------------+---------+-----+---------+------------+-------------+------------------+----------------------------+----------------------+------------+-------+------------------+----+--------------------+-----------------------------+--------------------+--------------------+-------------------

(0 rows)
Disabled Expanded output.
  • 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
---------------+------------+-------------+------------------+-------------------+------+----------+------

(0 rows)

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