oracle 与mysql数据库根据表名查询：表名，列明，列类型，类型长度，是否可为空，备注信息，是否为主键，是否为外键，外键所关联的表名称，外键所关联的字段名称

将your_table_name替换为你自己需要查询的表名

查询出来的信息可以用于代码生成器的输入进行代码生成或者进行数据库的设计导出

Oracle数据库查询语句

---

SELECT cols.table_name,
               cols.column_name,
               cols.data_type,
               cols.data_length,
               cols.nullable,
               com.comments,
               CASE
                   WHEN cons.constraint_type = 'P' THEN 'YES'
                   ELSE 'NO'
                   END                AS primary_key,
               CASE
                   WHEN cons.constraint_type = 'R' THEN 'YES'
                   ELSE 'NO'
                   END                AS foreign_key,
               r_cons.table_name      AS r_table_name,
               r_col_cons.column_name AS r_column_name
        FROM all_tab_columns cols
                 LEFT JOIN all_col_comments com
                           ON cols.table_name = com.table_name AND cols.column_name = com.column_name
                 LEFT JOIN (
            SELECT col_cons.table_name,
                   col_cons.column_name,
                   cons.constraint_type,
                   cons.r_constraint_name
            FROM all_cons_columns col_cons
                     JOIN all_constraints cons ON col_cons.constraint_name = cons.constraint_name
            WHERE cons.constraint_type IN ('P', 'R')
        ) cons ON cols.table_name = cons.table_name AND cols.column_name = cons.column_name
                 LEFT JOIN all_constraints r_cons ON cons.r_constraint_name = r_cons.constraint_name
                 LEFT JOIN all_cons_columns r_col_cons ON r_cons.constraint_name = r_col_cons.constraint_name
        WHERE cols.table_name = UPPER('your_table_name')

MySQL数据库查询语句

SELECT cols.TABLE_NAME,
       cols.COLUMN_NAME,
       cols.DATA_TYPE,
       cols.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH,
       cols.IS_NULLABLE              AS NULLABLE,
       cols.COLUMN_COMMENT           AS COMMENTS,
       CASE
           WHEN pk.COLUMN_NAME IS NOT NULL THEN 'YES'
           ELSE 'NO'
           END                       AS PRIMARY_KEY,
       CASE
           WHEN kcu.REFERENCED_TABLE_NAME IS NOT NULL THEN 'YES'
           ELSE 'NO'
           END                       AS FOREIGN_KEY,
       kcu.REFERENCED_TABLE_NAME     AS R_TABLE_NAME,
       kcu.REFERENCED_COLUMN_NAME    AS R_COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS cols
         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
                   ON cols.TABLE_NAME = kcu.TABLE_NAME AND cols.COLUMN_NAME = kcu.COLUMN_NAME AND
                      kcu.REFERENCED_TABLE_SCHEMA IS NOT NULL
         LEFT JOIN (
    SELECT k.TABLE_NAME, k.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
             JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
                  USING (CONSTRAINT_NAME, TABLE_NAME)
    WHERE t.CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND t.TABLE_SCHEMA = DATABASE()
) pk
                   ON cols.TABLE_NAME = pk.TABLE_NAME AND cols.COLUMN_NAME = pk.COLUMN_NAME
WHERE cols.TABLE_SCHEMA = DATABASE()
  AND cols.TABLE_NAME = 'your_table_name';

达梦数据库查询语句(外键部分未处理)

SELECT cols.table_name,
       cols.column_name,
       cols.data_type,
       cols.data_length,
       cols.nullable,
       col_com.comments,
       CASE
           WHEN pk.constraint_type = 'P' THEN 'YES'
           ELSE 'NO'
       END AS primary_key,
       'NO' AS foreign_key, -- 因为简化，这里不处理外键信息
       NULL AS r_table_name,
       NULL AS r_column_name
FROM user_tab_columns cols
LEFT JOIN user_col_comments col_com ON cols.table_name = col_com.table_name AND cols.column_name = col_com.column_name
LEFT JOIN (
    SELECT cons_col.table_name,
           cons_col.column_name,
           'P' AS constraint_type
    FROM user_cons_columns cons_col
    JOIN user_constraints cons ON cons_col.constraint_name = cons.constraint_name
    WHERE cons.constraint_type = 'P'
) pk ON cols.table_name = pk.table_name AND cols.column_name = pk.column_name
WHERE cols.table_name = UPPER('your_table_name');