How to list all cassandra tables

Question

There are many tables in cassandra database, which contain column titled user_id. The values user_id are referred to user stored in table users. As some users are deleted, I would like to delete orphan records in all tables that contain column titled user_id.

Is there a way to list all tables using CassandraSQLContext or any other built-in method or custom procedure in order to avoid explicitly defining the list of tables?

Bor

Answer 1

There are system tables which can provide information about stored keyspaces, tables, columns.

Try run follows commands in cqlsh console:

1. Get keyspaces info

   SELECT * FROM system.schema_keyspaces ;

2. Get tables info

   SELECT columnfamily_name FROM system.schema_columnfamilies WHERE keyspace_name = 'keyspace name';

3. Get table info

   SELECT column_name, type, validator FROM system.schema_columns WHERE keyspace_name = 'keyspace name' AND columnfamily_name = 'table name';

Since v 5.0.x Docs

1. Get keyspaces info

   SELECT * FROM system_schema.keyspaces;

2. Get tables info

   SELECT * FROM system_schema.tables WHERE keyspace_name = 'keyspace name';

3. Get table info

   SELECT * FROM system_schema.columns WHERE keyspace_name = 'keyspace_name' AND table_name = 'table_name';

Since v 6.0 Docs

1. Get keyspaces info

   SELECT * FROM system_schema.keyspaces

2. Get tables info

   SELECT * FROM system_schema.tables WHERE keyspace_name = 'keyspace name';

3. Get table info

   SELECT * FROM system_schema.columns WHERE keyspace_name = 'keyspace_name' AND table_name = 'table_name';

Disaffect

For my version of cassandra [cqlsh 5.0.1 | Cassandra 3.11.3 | CQL spec 3.4.4 | Native protocol v4] getting keyspace info works with SELECT * FROM system_schema.keyspaces – Agnail

And for getting tables info SELECT * FROM system_schema.tables WHERE keyspace_name = 'keyspace name' – Agnail

Did it change from system.schema_keyspaces to system_schema.keyspaces at some point? Otherwise you may have a typo in the first set of bullet points up there (I would edit it, but I don't have the background info to do so correctly I don't think). – Coop

Answer 2

From cqlsh execute describe tables;

Curson

This is the current and easiest way to list all tables – Consecutive

For the sake of completeness, run use my_keyspace; first. – Infold

Is it possible to alphabetize the output? – Baguette

worked for me, make sure to perform use keyspace_name; otherwise you will get result for All keyspaces. – Erlking

Answer 3

desc keyspaces;  // list all databases/collections names
use anyKeyspace;  // select any database
desc tables;      // list all tables in collection/ database

Triboluminescent

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Inlaid

Answer 4

You can achieve what you want using datastax core driver and cluster metadata. Here is an example which will list all the tables in your keyspace and columns in each table:

 Cluster cluster= Cluster.builder().addContactPoint(clusterIp).build();
    Metadata metadata =cluster.getMetadata();

   Collection<TableMetadata> tablesMetadata= metadata.getKeyspace("mykeyspacename").getTables();
    for(TableMetadata tm:tablesMetadata){
        System.out.println("Table name:"+tm.getName());
        Collection<ColumnMetadata> columnsMetadata=tm.getColumns();            
        for(ColumnMetadata cm:columnsMetadata){
            String columnName=cm.getName();
            System.out.println("Column name:"+columnName);
        }
    }

Default

Answer 5

For DSE. If later release check for system_schema keyspace. from

cqlsh > desc keyspaces;

spark_system  system_schema  "OpsCenter"  cfs_archive         "HiveMetaStore"
system_auth    cfs          demobeta            dsefs          
dse_security  hypermedia     dse_leases   system_traces       dse_perf       
solr_admin    system         system_distributed  dse_system

if you see 'system_schema' then the metadata for tables is in this keyspace.

cqlsh>use system_schema;

cqlsh>select keyspace_name,table_name from tables where keyspace_name = 'system';

Demosthenes

system keyspace isn't DSE-specific - it's a core Cassandra functionality... The schema representation is different between Cassandra versions, so the best way is to either use cqlsh's DESCRIBE KEYSPACES, or access via metadata as pointed by @zoran jeremic – Tisbee

alex, never meant to imply system keyspace is specific to DSE However some are . The schema tables were moved out of the 'system' keyspace so the tables system keyspace are not in that keyspace any longer .. this is a specific answer for DSE not Open Source Cassandra. Was referring to the metadata for DSE only . desc tables; as posted is really easy way to get a table list . comment was around the metadata for DSE not Open Source . If you are looking for those system tables not in DSE . if trying to generate a table list in your application sometimes can not run a desc but need to run cql... – Demosthenes

system keyspaces are specific for Cassandra. The difference that you describe is difference between Cassandra 2.x & 3.x (See dba.stackexchange.com/questions/134185/…). DSE specific keyspaces usually have the dse prefix, like, dse_security, dse_leases, etc. (although cfs & solr_admin don't follow this convention). Overall, if you need to access this data programmatically, then it's better to use drivers that support Metadata - it's then version agnostic. – Tisbee

Answer 6

To list all the tables (only the table names),

DESC tables;

To list all the tables and the schemas (CQL creation statements),

DESC {$KEYSPACE_NAME} whereas {$KEYSPACE_NAME} is the name of the keyspace.

I am using Apache Cassandra 3.11.4 binary package downloaded from the official site:

cql> show version;
[cqlsh 5.0.1 | Cassandra 3.11.4 | CQL spec 3.4.4 | Native protocol v4]

The Apache Cassandra's CQL reference is here: https://cassandra.apache.org/doc/cql3/CQL-3.0.html

Reverent

Answer 7

try running the following query:

cqlsh> describe keyspace <keyspace_name>;

Rafa

cqlsh: describe keyspace <keyspace_name>; – Rafa

Answer 8

Just connect CQLSH and try below commands

describe tables; or

DESC SCHEMA ;

Also you can 'desc keyspaces;' and 'use keyspace;' where you want to see the tables with 'describe tables;'

Overweight

describe schema is not CQL command, but cqlsh command... – Tisbee

@AlexOtt Yes, I meant after connecting cqlsh. – Overweight