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.

Query the defined keyspaces using the SELECT statement.

SELECT * FROM system_schema.keyspaces;

Results

 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'}
        cycling_alt |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
 system_distributed |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
             system |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
      system_traces |           True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}

(7 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;

Results

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

EXPAND set to ON

 keyspace_name | table_name | additional_write_policy | allow_auto_snapshot | 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 | incremental_backups | max_index_interval | memtable | memtable_flush_period_in_ms | min_index_interval | read_repair | read_repair_chance | speculative_retry
---------------+------------+-------------------------+---------------------+------------------------+---------+-----+---------+------------+-------------+------------------+----------------------------+----------------------+------------+-------+------------------+----+---------------------+--------------------+----------+-----------------------------+--------------------+-------------+--------------------+-------------------


(0 rows)
EXPAND set to OFF

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';

Results

 keyspace_name | table_name | column_name | clustering_order | column_name_bytes | kind | position | type
---------------+------------+-------------+------------------+-------------------+------+----------+------


(0 rows)

Keyspace, table, and column information

Was this helpful?

Give Feedback