Help us understand the problem. What is going on with this article?

テーブル一覧を取得するSQL(Oracle/MySQL/PostgreSQL/SQLite/SQLServer/Cassandra)

More than 1 year has passed since last update.

はじめに

MiluDBViewer(JavaFXを使ったGUI DBクライアント)
作ったときの内容を忘れてきたのでメモします。

今回は、テーブル一覧を取得するSQLについてです。

Oracle - 12c Release 2

select object_name, status from all_objects
where
object_type = 'TABLE'
and
owner = 'スキーマ名'
order by owner, object_name

statusの値域
VALID
INVALID

MySQL - 8.0.11/SQLServer - 2017

select  table_name from information_schema.tables 
where 
  table_type='BASE TABLE' 
  and 
  table_schema = 'スキーマ名'
order by table_name

MySQLとSQLServerが同じSQLで取得できる!!

PostgreSQL - 10.4

select 
  c.relname  
from 
  pg_class c join 
  pg_namespace n on n.oid = c.relnamespace 
where 
  n.nspname = 'スキーマ名' 
  and 
  c.relkind = 'r' 
order by c.relname

SQLite

select name from sqlite_master
where
  type = 'table'
order by name

Cassandra - 3.9.0

select table_name from system_schema.tables
where
  keyspae_name = 'スキーマ名'
order by table_name

上記以外のDB

後から知ったのですが、JDBCが実装していれば、
java.sql.DatabaseMeataDataのgetTables()メソッドで、
java.sql.ResultSetが返ってくるので、
resultset.getString("TABLE_NAME")でとれます。

参考URL
https://avaldes.com/jdbc-statement-databasemetadata-gettables-example/

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away