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?

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

Answer 2

From cqlsh execute describe tables;

Answer 3

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);
        }
    }

Answer 4

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

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

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

Answer 7

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

Answer 8

try running the following query:

cqlsh> describe keyspace <keyspace_name>;