导出oracle、postgresql、mysql表结构到excel
利用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;
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
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 = '换成指定数据库名'