如何编写SQL查询列出指定数据库所有表及其类型、属性数量

API 探路者

2026-10-1

需求实现SQL方案

以下按常用数据库分类给出对应查询语句，使用时替换语句中`指定数据库名`为你实际要查询的数据库名称即可。

MySQL

直接查询系统元数据表information_schema获取结果：

SELECT 
  t.TABLE_NAME AS 表名,
  t.TABLE_TYPE AS 表类型,
  COUNT(c.COLUMN_NAME) AS 字段总数量
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c 
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = '指定数据库名'
GROUP BY t.TABLE_NAME, t.TABLE_TYPE
ORDER BY t.TABLE_NAME;

返回的表类型字段值说明：`BASE TABLE`代表普通物理表，`VIEW`代表视图

PostgreSQL

可直接使用兼容SQL标准的information_schema查询：

SELECT 
  t.TABLE_NAME AS 表名,
  t.TABLE_TYPE AS 表类型,
  COUNT(c.COLUMN_NAME) AS 字段总数量
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c 
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = '指定数据库名'
  AND t.TABLE_SCHEMA = 'public' -- 可替换为实际要查询的schema名称
GROUP BY t.TABLE_NAME, t.TABLE_TYPE
ORDER BY t.TABLE_NAME;

SQL Server

USE 指定数据库名
GO
SELECT 
  t.name AS 表名,
  CASE 
    WHEN t.type = 'U' THEN 'BASE TABLE'
    WHEN t.type = 'V' THEN 'VIEW'
    ELSE t.type 
  END AS 表类型,
  COUNT(c.name) AS 字段总数量
FROM sys.tables t
LEFT JOIN sys.columns c ON t.object_id = c.object_id
GROUP BY t.name, t.type
ORDER BY t.name;