Navicat导出EXCEL表结构教程

通过 SQL 查询生成表结构并导出为 Excel

一、查询表结构信息：

打开 Navicat，连接到数据库。

使用 SQL 查询获取表结构信息。

结果

1. PostgreSQL 查询脚本

SELECT
    ROW_NUMBER() OVER (ORDER BY ordinal_position) AS "序号",
    c.column_name AS "列名",
    c.data_type AS "数据类型",
    CASE 
        WHEN c.character_maximum_length IS NOT NULL THEN c.character_maximum_length::TEXT
        WHEN c.numeric_precision IS NOT NULL THEN c.numeric_precision::TEXT || ',' || c.numeric_scale::TEXT
        ELSE ''
    END AS "长度/精度",
    CASE 
        WHEN kcu.column_name IS NOT NULL THEN '是'
        ELSE '否'
    END AS "主键",
    c.is_nullable AS "允许空",
    pgd.description AS "列说明"
FROM 
    information_schema.columns c
LEFT JOIN (
    SELECT 
        kcu.column_name
    FROM 
        information_schema.table_constraints tc
    JOIN 
        information_schema.key_column_usage kcu 
        ON tc.constraint_name = kcu.constraint_name
    WHERE 
        tc.table_name = 'cms_link' 
        AND tc.constraint_type = 'PRIMARY KEY'
) kcu 
    ON c.column_name = kcu.column_name
LEFT JOIN 
    pg_catalog.pg_statio_all_tables st 
    ON c.table_name = st.relname
LEFT JOIN 
    pg_catalog.pg_description pgd 
    ON pgd.objoid = st.relid 
    AND pgd.objsubid = c.ordinal_position
WHERE 
    c.table_name = 'cms_link'
ORDER BY 
    c.ordinal_position;

2. MySQL

SELECT
    ORDINAL_POSITION AS "序号",
    COLUMN_NAME AS "列名",
    COLUMN_TYPE AS "数据类型",
    CHARACTER_MAXIMUM_LENGTH AS "长度",
    CASE 
        WHEN COLUMN_KEY = 'PRI' THEN '是'
        ELSE '否'
    END AS "主键",
    IS_NULLABLE AS "允许空",
    COLUMN_COMMENT AS "列说明"
FROM 
    information_schema.columns
WHERE 
    TABLE_NAME = 'cms_link';

或者

SHOW FULL COLUMNS FROM cms_link;

3. SQL Server

SELECT
    ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS "序号",
    COLUMN_NAME AS "列名",
    DATA_TYPE AS "数据类型",
    CHARACTER_MAXIMUM_LENGTH AS "长度",
    CASE 
        WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 THEN '是'
        ELSE '否'
    END AS "主键",
    IS_NULLABLE AS "允许空",
    ep.value AS "列说明"
FROM 
    INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN 
    sys.extended_properties ep 
    ON ep.major_id = OBJECT_ID(c.TABLE_NAME) 
    AND ep.minor_id = c.ORDINAL_POSITION 
    AND ep.name = 'MS_Description'
WHERE 
    TABLE_NAME = 'cms_link';

4. Oracle 查询脚本

SELECT
    ROWNUM AS "序号",
    cols.column_name AS "列名",
    cols.data_type || 
    CASE 
        WHEN cols.data_type = 'NUMBER' AND cols.data_precision IS NOT NULL 
            THEN '(' || cols.data_precision || 
                CASE WHEN cols.data_scale > 0 THEN ',' || cols.data_scale ELSE '' END || ')'
        WHEN cols.data_type IN ('VARCHAR2', 'CHAR') 
            THEN '(' || cols.data_length || ')'
        ELSE ''
    END AS "数据类型",
    CASE
        WHEN cols.data_type = 'NUMBER' THEN cols.data_precision || ',' || cols.data_scale
        ELSE cols.data_length
    END AS "长度/精度",
    CASE 
        WHEN cons.constraint_type = 'P' THEN '是'
        ELSE '否'
    END AS "主键",
    cols.nullable AS "允许空",
    comm.comments AS "列说明"
FROM
    all_tab_columns cols
LEFT JOIN (
    SELECT
        cols.column_name,
        'P' AS constraint_type
    FROM
        all_constraints cons
    JOIN
        all_cons_columns cols
        ON cons.constraint_name = cols.constraint_name
    WHERE
        cons.table_name = 'CMS_LINK'  -- 注意 Oracle 默认表名大写
        AND cons.constraint_type = 'P'
) cons
    ON cols.column_name = cons.column_name
LEFT JOIN
    all_col_comments comm
    ON cols.table_name = comm.table_name
    AND cols.column_name = comm.column_name
WHERE
    cols.table_name = 'CMS_LINK'  -- 注意 Oracle 默认表名大写
ORDER BY
    cols.column_id;

5. 注意事项

1. 列说明：需提前在数据库中对字段添加注释（PostgreSQL 使用 COMMENT ON COLUMN，MySQL 使用 ALTER TABLE）。

2. 主键判断：如果表有复合主键，上述查询会为每个主键字段标记 是。

3. 数据库权限：确保连接用户有权限查询系统表（如 pg_catalog）。

二、导出查询结果为 Excel：

1. 导出表结构为 SQL 文件：

   • 右键点击目标表，选择“转储 SQL 文件” -> “仅结构”。

   • 保存 SQL 文件到本地。

2. 手动整理 SQL 文件为 Excel：

   • 打开 SQL 文件，提取表结构信息（如字段名、数据类型、是否为主键等）。

   • 将提取的信息整理到 Excel 表格中。