导出oracle、postgresql、mysql表结构到excel_pg库导出exlcel命令

利用sql查询出表结构数据，将查询结构导出成excel

1.Oracle表结构导出-库中的所有表

不需要改，粘过去执行即可

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default "默认值"
       --t4.created                                  AS "建表时间"
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
  ORDER BY t1.Table_Name, t1.Column_ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.postgresql导出表结构-指定表，需要一个个表查出来

需要在C.relname处改成每个指定表的表名

SELECT 
  A.attnum AS "序号",
    C.relname AS "表名",
    ---CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",
    A.attname AS "字段名",
		concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '(.*)' ) ) AS "数据类型",
		C.relhaspkey AS "是否主键",
		---IF(A.attnotnull='f','是','否') AS '必填',
    CASE A.attnotnull WHEN 'f' THEN 'Y' ELSE 'N' END,
--  A.attnotnull as 是否为空,
    ---IF(A.attnotnull='f','是','否') AS '必填',
    
    d.description AS "字段说明" 
FROM
    pg_class C,
    pg_attribute A,
    pg_type T,
    pg_description d 
WHERE
--指定的表
    C.relname = '指定的表名' 
    AND A.attnum > 0 
    AND A.attrelid = C.oid 
    AND A.atttypid = T.oid 
    AND d.objoid = A.attrelid 
    AND d.objsubid = A.attnum 
ORDER BY
    C.relname DESC,
    A.attnum ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3.mysql导出表结构-库中所有表

不需要改，粘过去执行即可

SELECT
TABLE_NAME 表名,
COLUMN_NAME 列名,
COLUMN_TYPE 数据类型,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否为空,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注 
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
-- 对应的数据库
table_schema = '换成指定数据库名'
1
2
3
4
5
6
7
8
9
10
11
12
13
14