2026-04-07 查询所有表结构
我来帮你编写查询数据库所有表结构的 SQL 语句。
MySQL 查询所有表结构的 SQL
1. 查询所有表的基本信息
-- 查询当前数据库中所有表的基本信息
SELECT
TABLE_NAME AS '表名',
TABLE_TYPE AS '表类型',
ENGINE AS '存储引擎',
TABLE_ROWS AS '估计行数',
DATA_LENGTH / 1024 / 1024 AS '数据大小(MB)',
INDEX_LENGTH / 1024 / 1024 AS '索引大小(MB)',
CREATE_TIME AS '创建时间',
UPDATE_TIME AS '更新时间',
TABLE_COMMENT AS '表注释'
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = DATABASE()
ORDER BY
CREATE_TIME DESC;
2. 查询指定表的所有字段信息
-- 查询所有表的字段详细信息
SELECT
TABLE_NAME AS '表名',
COLUMN_NAME AS '字段名',
COLUMN_TYPE AS '字段类型',
IS_NULLABLE AS '是否可空',
COLUMN_DEFAULT AS '默认值',
EXTRA AS '额外信息',
COLUMN_COMMENT AS '字段注释'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = DATABASE()
ORDER BY
TABLE_NAME, ORDINAL_POSITION;
3. 查询所有表的索引信息
-- 查询所有表的索引信息
SELECT
TABLE_NAME AS '表名',
INDEX_NAME AS '索引名',
COLUMN_NAME AS '列名',
NON_UNIQUE AS '是否唯一(0-唯一)',
INDEX_TYPE AS '索引类型',
COLLATION AS '排序规则'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = DATABASE()
ORDER BY
TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
4. 查询所有表的外键约束
-- 查询所有表的外键约束
SELECT
TABLE_NAME AS '子表',
COLUMN_NAME AS '子表字段',
REFERENCED_TABLE_NAME AS '父表',
REFERENCED_COLUMN_NAME AS '父表字段',
CONSTRAINT_NAME AS '约束名'
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = DATABASE()
AND REFERENCED_TABLE_NAME IS NOT NULL;
5. 综合查询：所有表的完整结构（包含字段）
-- 综合查询：获取所有表的完整结构（每个表的字段列表）
SELECT
c.TABLE_NAME AS '表名',
c.COLUMN_NAME AS '字段名',
c.COLUMN_TYPE AS '字段类型',
c.IS_NULLABLE AS '是否可空',
IFNULL(c.COLUMN_DEFAULT, 'NULL') AS '默认值',
c.EXTRA AS '额外属性',
c.COLUMN_COMMENT AS '字段注释',
t.TABLE_COMMENT AS '表注释'
FROM
information_schema.COLUMNS c
LEFT JOIN information_schema.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE
c.TABLE_SCHEMA = DATABASE()
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION;
6. 生成建表语句（查看完整 DDL）
-- 查看指定表的建表语句
SHOW CREATE TABLE users;
SHOW CREATE TABLE roles;
SHOW CREATE TABLE menus;
SHOW CREATE TABLE refresh_tokens;
SHOW CREATE TABLE verification_codes;
SHOW CREATE TABLE password_reset_tokens;
SHOW CREATE TABLE social_logins;
SHOW CREATE TABLE user_registrations;
SHOW CREATE TABLE api_logs;
SHOW CREATE TABLE error_logs;
SHOW CREATE TABLE scheduled_tasks;
SHOW CREATE TABLE notifications;
SHOW CREATE TABLE notices;
SHOW CREATE TABLE api_configs;
SHOW CREATE TABLE lowcode_entity;
SHOW CREATE TABLE lowcode_field;
SHOW CREATE TABLE lowcode_data;
-- 或者批量获取所有表的建表语句
SELECT
CONCAT('SHOW CREATE TABLE ', TABLE_NAME, ';') AS show_create_sql
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = DATABASE();
7. 一键导出所有表结构（生成完整的 SQL 导出脚本）
-- 生成所有表的建表语句（用于备份/导出）
SELECT
CONCAT(
'-- ', TABLE_NAME, '\n',
'DROP TABLE IF EXISTS `', TABLE_NAME, '`;\n',
'CREATE TABLE `', TABLE_NAME, '` (\n',
GROUP_CONCAT(
CONCAT(
' `', COLUMN_NAME, '` ',
COLUMN_TYPE,
IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
IFNULL(CONCAT(' DEFAULT ',
CASE
WHEN COLUMN_DEFAULT IS NULL THEN 'NULL'
WHEN COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN 'CURRENT_TIMESTAMP'
ELSE CONCAT('\'', COLUMN_DEFAULT, '\'')
END
), ''),
IF(EXTRA != '', CONCAT(' ', EXTRA), ''),
IF(COLUMN_COMMENT != '', CONCAT(' COMMENT \'', COLUMN_COMMENT, '\''), '')
)
ORDER BY ORDINAL_POSITION
SEPARATOR ',\n'
),
'\n) ENGINE=', ENGINE,
' DEFAULT CHARSET=utf8mb4',
IF(TABLE_COMMENT != '', CONCAT(' COMMENT=\'', TABLE_COMMENT, '\''), ''),
';\n'
) AS create_table_sql
FROM
information_schema.COLUMNS c
JOIN information_schema.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE
c.TABLE_SCHEMA = DATABASE()
GROUP BY
c.TABLE_NAME, t.ENGINE, t.TABLE_COMMENT;
8. 快速查看表结构汇总（推荐）
-- 快速汇总：显示所有表的关键信息
SELECT
t.TABLE_NAME AS '表名',
COUNT(c.COLUMN_NAME) AS '字段数',
t.TABLE_ROWS AS '行数(估)',
ROUND(t.DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
ROUND(t.INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)',
t.TABLE_COMMENT AS '表注释'
FROM
information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE
t.TABLE_SCHEMA = DATABASE()
GROUP BY
t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, t.TABLE_COMMENT
ORDER BY
t.TABLE_NAME;
使用方式
方式一：直接在 MySQL 客户端执行
# 登录 MySQL
mysql -u root -p
# 选择数据库
USE your_database_name;
# 执行上述任意 SQL 语句
方式二：通过命令行导出
# 导出所有表结构（不包括数据）
mysqldump -u root -p --no-data your_database_name > database_structure.sql
# 导出指定表的结构
mysqldump -u root -p --no-data your_database_name users roles menus > tables_structure.sql
方式三：使用 Node.js 脚本查询
// query-tables.ts
import { DataSource } from 'typeorm';
import { config } from 'dotenv';
config();
const AppDataSource = new DataSource({
type: 'mysql',
host: process.env.DATABASE_HOST || 'localhost',
port: parseInt(process.env.DATABASE_PORT || '3306'),
username: process.env.DATABASE_USERNAME || 'root',
password: process.env.DATABASE_PASSWORD || '',
database: process.env.DATABASE_NAME || 'nest_app',
});
async function queryTableStructure() {
await AppDataSource.initialize();
// 查询所有表
const tables = await AppDataSource.query(`
SELECT
TABLE_NAME AS tableName,
TABLE_COMMENT AS tableComment,
CREATE_TIME AS createTime
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME
`);
console.log('='.repeat(80));
console.log('数据库表结构汇总');
console.log('='.repeat(80));
for (const table of tables) {
console.log(`\n📋 表名: ${table.tableName}`);
console.log(` 注释: ${table.tableComment || '无'}`);
// 查询表的字段
const columns = await AppDataSource.query(`
SELECT
COLUMN_NAME AS columnName,
COLUMN_TYPE AS columnType,
IS_NULLABLE AS isNullable,
COLUMN_DEFAULT AS defaultValue,
EXTRA AS extra,
COLUMN_COMMENT AS comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION
`, [table.tableName]);
console.log(` 字段数: ${columns.length}`);
console.log(' 字段列表:');
for (const col of columns) {
const nullable = col.isNullable === 'NO' ? 'NOT NULL' : '';
const defaultVal = col.defaultValue ? `DEFAULT ${col.defaultValue}` : '';
console.log(` - ${col.columnName}: ${col.columnType} ${nullable} ${defaultVal} ${col.extra} -- ${col.comment || ''}`);
}
}
await AppDataSource.destroy();
}
queryTableStructure().catch(console.error);
运行脚本：
npx ts-node src/scripts/query-tables.ts
浙公网安备 33010602011771号