出DB文档用SQL，DB2+MYSQL

最新推荐文章于 2024-11-25 15:57:15 发布

原创最新推荐文章于 2024-11-25 15:57:15 发布 · 133 阅读

0 ·

CC 4.0 BY-SA版权

mysql 同时被 2 个专栏收录

11 篇文章

订阅专栏

db2

1 篇文章

订阅专栏

本文详细介绍如何使用SQL语句查询数据库表的元数据信息，包括表名、字段名、数据类型、字段长度、小数位数、是否允许为空、是否为主键、默认值及字段注释等关键信息。适用于MySQL和DB2两种数据库系统。

MYSQL：
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='skysupport';


select ORDINAL_POSITION as '序号',COLUMN_NAME as '名称',DATA_TYPE as '数据类型',CHARACTER_MAXIMUM_LENGTH as '长度',
    NUMERIC_SCALE as '小数位',IS_NULLABLE as '允许为空',
    (CASE 
        WHEN COLUMN_KEY='PRI'
        THEN '是'
        ELSE '否'
        END
    ) as '是否主键' ,
    COLUMN_DEFAULT as '默认值',
    COLUMN_COMMENT as '说明'
    from information_schema.columns
    where table_schema = 'skysupport' 
    and table_name = 'business_module' ; #你要查的表

select COLUMN_NAME as '名称',
    COLUMN_COMMENT as '中文注释',
    DATA_TYPE as '数据类型',
    '' as '备注'
    from information_schema.columns
    where table_schema = 'taxany_sy'#表所在数据库
    and table_name = 'D005_TB_TAX' ; #你要查的表


DB2:

SELECT t.colno+1 as 序号, 
     t.ColName as 名称,
     lower(t.TypeName) as 数据类型,
     t.Length as 长度,
     t.SCALE AS 小数位,
     t."NULLS" AS 允许为空,
     CASE t.KEYSEQ 
        WHEN 1  THEN '是'
        ELSE '否' 
    END as 是否主键,
    t.DEFAULT AS 默认值,
     t.Remarks as Remark  
FROM syscat.COLUMNS t 
WHERE tabschema='DB2INST1' AND tabname=upper('D005_TB_TAX')
ORDER BY 序号 ASC;

SELECT 
     t.ColName as 名称,
     t.Remarks as 中文注释,
     lower(t.TypeName) as 数据类型,
     '' AS 备注
FROM syscat.COLUMNS t 
WHERE tabschema='DB2INST1' AND tabname=upper('D005_TB_TAX')
ORDER BY t.colno ASC;