达梦8 查询表、字段、索引、约束等系统表常用语句

系统表常用查询

select * from all_tables; -- 所有表  
select * from all_tab_comments; -- 所有表注释  
select * from all_tab_columns; -- 所有表字段  
select * from all_col_comments; -- 所有字段注释  
select * from all_users; -- 所有用户  
select * from all_views; -- 所有视图  
select * from all_sequences; -- 所有序列  
select * from all_constraints; -- 所有约束  
select * from all_cons_columns; -- 所有约束字段  
select * from all_ind_columns; -- 所有索引字段  
select * from all_indexes; -- 所有索引  
select * from all_triggers; -- 所有触发器

组合栗子

• 查询表空间所有表

select t.OWNER, t.TABLE_NAME, c.comments  
from all_tables t,  
    all_tab_comments c  
where t.TABLE_NAME = c.TABLE_NAME  
    and t.OWNER = 'XXX'  
    and c.OWNER = 'XXX';

• 查询表空间所有字段

select t.TABLE_NAME, t.COLUMN_NAME, c.comments  
from all_tab_columns t,  
    all_col_comments c  
where t.TABLE_NAME = c.TABLE_NAME  
    and t.COLUMN_NAME = c.COLUMN_NAME  
    and t.OWNER = 'XXX'  
    and c.OWNER = 'XXX';

• 查询表空间所有索引

select t.TABLE_NAME, t.INDEX_NAME, t.COLUMN_NAME, t.COLUMN_POSITION  
from all_ind_columns t  
where t.TABLE_OWNER = 'XXX'  
    and t.TABLE_NAME = 'XXX';