达梦数据库获取表设计

[toplhy]

1. 查询模式下所有表

SELECT T.TABLE_NAME, C.COMMENTS
FROM DBA_TABLES T
LEFT JOIN ALL_TAB_COMMENTS C ON (C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME)
WHERE T.OWNER = '具体模式名'
ORDER BY T.TABLE_NAME ASC;

2. 查询表字段设计

SELECT COL.COLUMN_NAME "字段名称",
       COL.DATA_TYPE "字段类型",
       COL.DATA_LENGTH "字段长度",
       (CASE WHEN NULLABLE = 'N' THEN '否' ELSE '是' END) "是否为空",
       COM.COMMENTS "字段描述"
FROM DBA_TAB_COLUMNS COL
LEFT JOIN DBA_COL_COMMENTS COM ON (COM.OWNER = COL.OWNER AND COM.TABLE_NAME = COL.TABLE_NAME AND COM.COLUMN_NAME = COL.COLUMN_NAME)
WHERE COL.OWNER = '具体模式名'
  AND COL.TABLE_NAME = '具体表名';

3. 整体查询

SELECT T.TABLE_NAME "表名",
       C.COMMENTS "表注释",
       COL.COLUMN_NAME "字段名称",
       COL.DATA_TYPE "字段类型",
       COL.DATA_LENGTH "字段长度",
       (CASE WHEN NULLABLE = 'N' THEN '否' ELSE '是' END) "是否为空",
       COM.COMMENTS "字段描述"
FROM DBA_TABLES T
LEFT JOIN ALL_TAB_COMMENTS C ON (C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME)
LEFT JOIN DBA_TAB_COLUMNS COL ON (COL.OWNER = T.OWNER AND COL.TABLE_NAME = T.TABLE_NAME)
LEFT JOIN DBA_COL_COMMENTS COM ON (COM.OWNER = COL.OWNER AND COM.TABLE_NAME = COL.TABLE_NAME AND COM.COLUMN_NAME = COL.COLUMN_NAME)
WHERE T.OWNER = '具体模式名'
ORDER BY T.TABLE_NAME ASC, COL.COLUMN_ID ASC;