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 thisSELECT
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'} keyspace1 | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'} 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'} (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 cqlshEXPAND 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 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 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: Thesystem_schema
tables do NOT show search index or row-level access control settings.