达梦/ MySQL/人大金仓，达梦 Dba / 非Dba权限，查询模式 / 库下所有表名

达梦：//某模式下所有表名AND SEGMENT_NAME LIKE 'CD_%'select owner,SEGMENT_NAME as tbName from dba_segmentswhere segment_type='TABLE' and OWNER ='模式名'//所有表select table_name as tbName from user_tables wheretable_na

niceguyº

10046人浏览 · 2022-01-23 17:03:47

niceguyº · 2022-01-23 17:03:47 发布

1、达梦：
查询 dba_segments 需要Dba权限，用户没有Dba权限时查询 all_tables 或 user_tables

# 某模式下所有表名  需要DBA权限          AND SEGMENT_NAME LIKE 'CD_%' 
select owner,SEGMENT_NAME as tbName from dba_segments  where segment_type='TABLE' and OWNER ='模式名' 

# 查看模式下所有表  不需要DBA权限       and TABLE_NAME LIKE 'CD_%'
select TABLE_NAME as tbName from all_tables where OWNER ='模式名' 

# 用户下所有表  		where  table_name like 'CD_%' 
select table_name as tbName from user_tables group by TABLE_NAME

2、MySQL：

# 某个数据库下所有表名  where  table_name like 'CD_%'
SELECT table_name as tbName,table_schema from information_schema.columns 
                join (select database() tbName) b on table_schema= '库名' group by table_name
# or
SELECT table_name as tbName from information_schema.TABLES

3、人大金仓：

# 所有
SELECT table_name as tbName from information_schema.TABLES
# public 模式
SELECT table_name as tbName from information_schema.TABLES WHERE  table_schema='public'