Postgresql、Mysql、Oracle查询库、schema、表

Postgresql

1、查询pg的内置视图pg_tables:

select * from pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;

结果：

主要有三个字段：schema  tablename  tableowner

前两个好理解，第三个是表所属，就是谁建的，通常跟库名一样（要是不一样就有点扯了。。）

2、根据库名查看所有schema及表名

select concat(concat(a.schemaname,'.'),a.tablename) as tablename
from pg_tables a
join pg_user b on a.tableowner=b.usename
join pg_database c on b.usesysid=c.datdba
where c.datname='dataflow'
order by concat(concat(a.schemaname,'.'),a.tablename);

 关联系统用户表、数据库表，再结合拼装函数，组成需要的   schema.tablename

Mysql

1.查询mysql内置表information_schema.tables

select concat(concat(table_schema,'.'),table_name) tablename from information_schema.tables where table_schema='test' order by concat(concat(table_schema,'.'),table_name)

Oracle

1.查询oracle内置表dba_tables

select concat(concat(owner,'.'),table_name) tablename from dba_tables where owner = 'test' order by concat(concat(owner,'.'),table_name)