Mysql及Oracle数据库导出数据表结构

Mysql及Oracle数据库导出数据表结构

废话不多说，直接上代码。

Mysql

select 
distinct
column_comment AS "字段名称",
column_name AS "名称",
DATA_TYPE AS "字段数据类型",
case IS_NULLABLE when "YES" then "否" ELSE "是" END "必填",
ifnull(CHARACTER_MAXIMUM_LENGTH,0) "值域",
column_comment AS "备注"
from information_schema.columns 
where 
table_name = 'MJFLSZ' ;

Oracle

SELECT 
    B.COLUMN_ID AS "字段序号", 
    B.TABLE_NAME AS "表名",
--     C.COMMENTS  AS "表说明", 
    A.COMMENTS  AS "字段名称",
    B.COLUMN_NAME  AS "名称", 
    B.DATA_TYPE AS "字段数据类型", 
		decode(B.NULLABLE,'Y','否','是') 必填,
-- 		B.NULLABLE AS "可空",
		decode(B.DATA_LENGTH,'22','18,2',B.DATA_LENGTH) 值域,
    A.COMMENTS  AS "备注"
--     B.DATA_LENGTH  AS "数据长度", 
--     B.DATA_PRECISION AS "整数位",        
--     B.DATA_SCALE  AS "小数位",
FROM 
    ALL_COL_COMMENTS A, 
    ALL_TAB_COLUMNS B, 
    ALL_TAB_COMMENTS C 
WHERE 
    A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)    
    AND A.OWNER = B.OWNER    
    AND A.TABLE_NAME = B.TABLE_NAME    
    AND A.COLUMN_NAME = B.COLUMN_NAME    
    AND C.TABLE_NAME = A.TABLE_NAME    
    AND C.OWNER = A.OWNER    
     AND A.OWNER = 'GDHTSUPERVISION' --用户名（区分大小写）
    AND A.TABLE_NAME = 'MJFLSZ' --表名（区分大小写）
ORDER BY A.TABLE_NAME, B.COLUMN_ID;

PS：我是青春代码，山高路远，江湖再见。
友情提示：创作不易，转载请注明出处。