【达梦】查询表,字段,索引,约束等常用语句
查看表(库,表)
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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性