CODEWITHSUNDEEP
scalaapache-sparkcassandraspark-cassandra-connector
Niko Gamulin
Niko Gamulin

Reputation: 66565

How to list all Cassandra tables

There are many tables in a Cassandra database, which contain a column titled user_id. The values in user_id refer to users stored in the table users. As some users are deleted, I would like to delete orphan records in all tables that contain column a 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?

Upvotes: 87

Views: 202164

Answers (8)

Ruslan Gafiullin
Ruslan Gafiullin

Reputation: 21

try running the following query:

cqlsh> describe keyspace <keyspace_name>;

Upvotes: 2

shubham kumar
shubham kumar

Reputation: 331

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

Upvotes: 26

LetsNoSQL
LetsNoSQL

Reputation: 1538

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

Upvotes: 1

Sabik
Sabik

Reputation: 1679

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

Upvotes: 110

Devy
Devy

Reputation: 10189

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

Upvotes: 4

mipsbuster
mipsbuster

Reputation: 51

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

Upvotes: 5

Dan Borza
Dan Borza

Reputation: 3549

From cqlsh execute describe tables;

Upvotes: 159

zoran jeremic
zoran jeremic

Reputation: 2138

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

Upvotes: 7

Related Questions