【达梦】查询表，字段，索引，约束等常用语句

查看表（库，表）

select table_name from user_tables; //当前用户的表 （这个查询的表比较少）
select table_name from all_tables; //所有用户的表 （全部的表）
select table_name from dba_tables; //包括系统表（表也很少）
select table_name from dba_tables where owner='用户名';

select * from user_tab_columns where Table_Name='XX';

查看表字段

select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';

注解

select * from user_tab_comments; // 表注释
select*from user_col_comments; // 字段注释
user_col_comments：table_name,column_name,comments

查询DM数据库所有表的各种约束和索引

--查询主键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='P';

--查询外键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='R';

--查询唯一约束
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='U';

--查询check约束
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名",SEARCH_CONDITION as "CHECK约束的条件"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='C';

--查询默认列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型",DATA_DEFAULT as "默认值" FROM DBA_TAB_COLUMNS WHERE DATA_DEFAULT IS NOT NULL;

--查询非空列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型" FROM DBA_TAB_COLUMNS WHERE NULLABLE ='Y';

--查询索引
SELECT TABLE_OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",INDEX_NAME as "索引名" from DBA_IND_COLUMNS WHERE TABLE_OWNER='T1';

---

参考： https://blog.csdn.net/weixin_44312518/article/details/117326846