How to list all the available keyspaces in Cassandra?

Question

I am newbie in Cassandra and trying to implement one toy application using Cassandra. I had created one keyspace and few column families in my Cassandra DB but I forgot the name of my cluster.

I am trying to find if there is any query which can list down all the available keyspaces.

Anybody knows such a query or command?

[C* 3.x](https://dba.stackexchange.com/questions/134185/migration-from-cassandra-2-x-to-3-x-schema-keyspaces-table-is-gone-how-do-you) : SELECT * FROM system_schema.keyspaces; — belgacea, Jul 12 '16 at 13:50
This answer is outdated. Correct answer is in here [enter link description here](http://stackoverflow.com/questions/16612463/is-there-a-clear-equivalent-of-show-keyspaces-in-cqlsh-2) — Vinaya Thimmappa, Apr 14 '17 at 05:58
Possible duplicate of [Is there a clear equivalent of 'show keyspaces' in cqlsh 2?](https://stackoverflow.com/questions/16612463/is-there-a-clear-equivalent-of-show-keyspaces-in-cqlsh-2) — zurfyx, Apr 08 '18 at 16:58

score 449 · Answer 1 · edited Jul 16 '22 at 19:13

449

[cqlsh 4.1.0 | Cassandra 2.0.4 | CQL spec 3.1.1 | Thrift protocol 19.39.0]

Currently, the command to use is:

DESC[RIBE] keyspaces;

edited Jul 16 '22 at 19:13

mirekphd

4,799
3
38
59

answered Jan 17 '14 at 09:20

Marco Ponzi

4,638
2
12
5

13

Accept this as answer! And by the way, the output would print multiple keyspace names in one line. – Eric Jul 04 '16 at 13:59
5

Cassandra also allow a short-hand command as in: DESC keyspaces – Hari Dec 03 '17 at 04:03

score 106 · Accepted Answer · answered Sep 11 '13 at 19:23

106

If you want to do this outside of the cqlsh tool you can query the schema_keyspaces table in the system keyspace. There's also a table called schema_columnfamilies which contains information about all tables.

The DESCRIBE and SHOW commands only work in cqlsh and cassandra-cli.

answered Sep 11 '13 at 19:23

Theo

131,503
21
160
205

1

Too much detail sometimes takes away simplicity, Marco answer is on point. – Neeraj Jain Jan 28 '21 at 10:36
1

`show keyspaces` in `sqlsh` in Cassandra 4.0 it raises an error ("Improper show command.") I used `describe keyspaces` instead. – mirekphd Jul 16 '22 at 18:11
did you mean SELECT * FROM system_schema.keyspaces? instead of SELECT * FROM system.schema.keyspaces? Please edit your answer... – g.pickardou Oct 18 '22 at 13:20

score 53 · Answer 3 · edited Dec 27 '17 at 16:16

53

Its very simple. Just give the below command for listing all keyspaces.

Cqlsh> Describe keyspaces;

If you want to check the keyspace in the system schema using the SQL query

below is the command.

SELECT * FROM system_schema.keyspaces;

Hope this will answer your question...

You can go through the explanation on understanding and creating the keyspaces from below resources.

Documentation:

https://docs.datastax.com/en/cql/3.1/cql/cql_reference/create_keyspace_r.html https://www.i2tutorials.com/cassandra-tutorial/cassandra-create-keyspace/

edited Dec 27 '17 at 16:16

Ronnie Oosting

1,252
2
14
35

answered Dec 27 '17 at 14:21

Prasu

533
4
5

DESCRIBE does not work on latest cqlsh. This works – Rakesh Malik Nov 09 '21 at 13:49

Shekhar · Answer 4 · 2015-05-28T05:45:45.540

13

Found it...show keyspaces command lists down all the keyspaces. I think earlier when I tried this command, I forgot to give last 's' in 'keyspaces'

edited May 28 '15 at 05:45

answered Sep 10 '13 at 07:33

Shekhar

11,438
36
130
186

8

You can use tab completion to see hints about how to complete a cassandra-cli or cqlsh command. That may be helpful to you if you forget the syntax. For example, just type "show " and then tab to see the valid show commands. – Ike Walker Sep 11 '13 at 17:03
3

The secondary point in your answer is going to be (for some people) the most valuable part of the answer. Things work a lot differently between "cqlsh" and "cassandra-cli" (and the question poster does not indicate which he is using). show keyspaces; describe (keyspace name). NOTE: If one is using "cqlsh", it requires quotes around any mixed or upper case names in a keyspace or columnfamily (OK, the poster didn't ask this but it's a common user error if new to C*) – Scott Prive Feb 03 '15 at 01:35
1

In `sqlsh` in Cassandra 4.0 it raises an error ("Improper show command.") I used `describe keyspaces` instead. – mirekphd Jul 16 '22 at 18:10

score 10 · Answer 5 · edited Apr 09 '20 at 08:37

10

To see all the keyspaces on your Apache Cassandra NoSQL Database Server use the command:

> DESCRIBE KEYSPACES

edited Apr 09 '20 at 08:37

סטנלי גרונן

2,917
23
46
68

answered Apr 09 '20 at 08:06

Suman Gangopadhyay

111
1
4

This should be the accepted answer. Short and directly to the point. – nocdib Oct 14 '20 at 19:23

score 7 · Answer 6 · edited Oct 15 '19 at 11:57

7

Once logged in to cqlsh or cassandra-cli. run below commands

On cqlsh

desc keyspaces;

or

describe keyspaces;

or

select * from system_schema.keyspaces;

On cassandra-cli

show keyspaces;

edited Oct 15 '19 at 11:57

Alex Ott

80,552
8
87
132

answered Dec 19 '18 at 15:23

LetsNoSQL

1,478
1
11
23

score 5 · Answer 7 · answered Sep 11 '17 at 11:03

The DESCRIBE command is your friend. You can describe one keyspace, list keyspaces, one table or list all tables in keyspace, the cluster and much more. You can get the full idea by typing

HELP DESCRIBE in cqlsh.

Connected to mscluster at 127.0.0.1:9042. [cqlsh 5.0.1 | Cassandra 3.8 | CQL spec 3.4.2 | Native protocol v4] Use HELP for help.

cqlsh> HELP DESCRIBE
    DESCRIBE [cqlsh only]

    (DESC may be used as a shorthand.)

      Outputs information about the connected Cassandra cluster, or about
      the data objects stored in the cluster. Use in one of the following ways:...<omitted for brevity>

DESCRIBE <your key space name> - describes the command used to create keyspace

cqlsh> DESCRIBE testkeyspace;

CREATE KEYSPACE testkeyspace WITH replication = {'class':'SimpleStrategy', 'replication_factor': '3'} AND durable_writes = true;

DESCRIBE keyspaces - lists all keyspaces

cqlsh> DESCRIBE KEYSPACES

system_schema system testkeyspace system_auth
system_distributed system_traces

DESCRIBE TABLES - List all tables in current keyspace

cqlsh:system> DESCRIBE TABLES;

available_ranges peers paxos
range_xfers batches compaction_history batchlog
local "IndexInfo" sstable_activity
size_estimates hints views_builds_in_progress peer_events
built_views

DESCRIBE your table name or DESCRIBE TABLE your table name - Gives the table details

cqlsh:system> DESCRIBE TABLE batchlog

CREATE TABLE system.batchlog ( id uuid PRIMARY KEY, data blob, version int, written_at timestamp ) WITH bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'} AND comment = 'DEPRECATED batchlog entries' ....omitted for brevity

score 5 · Answer 8 · answered Mar 13 '20 at 08:47

5

login to cqlsh

use below command to get names/list of keyspaces present

     SELECT keyspace_name FROM system_schema.keyspaces;

answered Mar 13 '20 at 08:47

Suparna Raut

71
1
5

1

it depends on the version of Cassandra... Also, how this answer is different from previous? – Alex Ott Mar 13 '20 at 11:48

score 4 · Answer 9 · answered Nov 22 '17 at 14:57

4

DESC KEYSPACES will do the job.

Also, If you want to describe schema of a particular keyspace you can use

DESC

answered Nov 22 '17 at 14:57

DataJanitor

456
2
4
16

score 4 · Answer 10 · answered Oct 23 '20 at 09:14

4

To List all the available keyspaces in cassandra using cqlsh in CLI mode.

Command : DESCRIBE keyspaces;

Example :

cqlsh> DESCRIBE keyspaces;

answered Oct 23 '20 at 09:14

Saptak P

76
1
10

score 2 · Answer 11 · answered Nov 11 '18 at 02:21

2

login to cqlsh
desc keyspaces;
select * from system_schema.keyspaces ;

answered Nov 11 '18 at 02:21

Jay Tokala

36
2

score 1 · Answer 12 · answered Oct 26 '17 at 11:51

1

desc keyspaces will do it for you.

answered Oct 26 '17 at 11:51

Chakri Stark

166
1
1
12

score 1 · Answer 13 · answered May 09 '19 at 08:57

1

DESCRIBE keyspaces to list all keysapces DESCRIBE keyspace https://docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/cqlsh_commands/cqlshDescribeKeyspace.html

answered May 09 '19 at 08:57

Puneet

113
5

score 1 · Answer 14 · edited Sep 05 '19 at 16:05

1

I suggest a combination of grep and awk:

root@DC1-Node1:/home# nodetool tablestats | grep "Keyspace :" | awk -F ":" '{print $2}'
 system_traces
 system
 system_distributed
 system_schema
 device_tool
 system_tool

edited Sep 05 '19 at 16:05

René Vogt

43,056
14
77
99

answered Sep 05 '19 at 13:33

Mazhar Shaikh

11
1

thank you for the answer but simplest way to see keyspaces is to use "DESCRIBE keyspace" command. No need to grep and awk. – Shekhar Sep 05 '19 at 13:38

score 1 · Answer 15 · edited Mar 24 '23 at 17:57

1

Defining A KeySpace

Understanding A Database:-In Cassandra, A database is defined as a Keyspace, Within the KeySpace Table Can be Defined.

Checking ALL KeySpace

cqlsh> DESCRIBE KEYSPACEs;

Defining A Available Keyspace

cqlsh> DESCRIBE KEYSPACE stack ;

edited Mar 24 '23 at 17:57

Wahyu Kristianto

8,719
6
43
68

answered Mar 17 '23 at 11:15

Sumit_Keen

31
2

score 0 · Answer 16 · answered Feb 19 '19 at 17:48

Apart from above method, if you have opscenter installed,

Go to data tab > there you will see all keyspcaces created by you and some system keyspaces.
You can see all tables under individual keyspaces and also replicator factor for keyspace.

for more details check below link. https://docs.datastax.com/en/opscenter/6.1/opsc/online_help/opscDataModelingManagingKeyspace_t.html

score -1 · Answer 17 · answered Oct 15 '19 at 10:24

-1

describes and desc command will give list of keyspaces in the cluster.Please find below output for more details.

cqlsh> describe keyspaces
reaper_db      system_auth  system_distributed
system_schema  system       system_traces

OR

cqlsh> desc keyspaces
reaper_db      system_auth  system_distributed
system_schema  system       system_traces

answered Oct 15 '19 at 10:24

Srikant Patra

399
4
5

how is your answer is different from other answers? Also - these commands are only working in `cqlsh`, not everywhere... – Alex Ott Oct 15 '19 at 11:57
cassandra@node1:~$ nodetool tablestats | grep "Keyspace :" Keyspace : reaper_db Keyspace : system_traces Keyspace : system Keyspace : system_distributed Keyspace : system_schema Keyspace : system_auth cassandra@node1:~$ – Srikant Patra Oct 15 '19 at 15:18
There are at least 4 answers that list the same commands for cqlsh – Alex Ott Oct 15 '19 at 15:49

How to list all the available keyspaces in Cassandra?

17 Answers17

Linked