获取SQL Server数据库所有表的SQL语句（含系统架构表）

SQL Server 方案 #

要覆盖所有用户表、INFORMATION_SCHEMA对象以及sys架构下的系统表，可以联合查询INFORMATION_SCHEMA.TABLES和系统视图sys.system_tables：

SELECT 
    TABLE_CATALOG AS [目录],
    TABLE_SCHEMA AS [架构],
    TABLE_NAME AS [表名],
    TABLE_TYPE AS [类型]
FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT 
    DB_NAME() AS [目录],
    s.name AS [架构],
    t.name AS [表名],
    'SYSTEM TABLE' AS [类型]
FROM sys.system_tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY [架构], [表名];

• INFORMATION_SCHEMA.TABLES返回用户创建的表、视图，以及INFORMATION_SCHEMA自身的视图对象
• sys.system_tables包含SQL Server内置的系统表，通过关联sys.schemas获取架构名

MySQL 方案 #

MySQL的INFORMATION_SCHEMA.TABLES本身包含所有数据库的表信息，只需指定目标数据库、information_schema和sys架构即可：

SELECT 
    TABLE_CATALOG AS `目录`,
    TABLE_SCHEMA AS `架构`,
    TABLE_NAME AS `表名`,
    TABLE_TYPE AS `类型`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN (DATABASE(), 'information_schema', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

• DATABASE()函数自动获取当前连接的指定数据库
• 过滤条件包含sys架构（MySQL 5.7+引入的系统架构）

PostgreSQL 方案 #

PostgreSQL的系统表集中在pg_catalog架构，结合information_schema和用户常用的public架构：

SELECT 
    table_catalog AS "目录",
    table_schema AS "架构",
    table_name AS "表名",
    table_type AS "类型"
FROM information_schema.tables
WHERE table_schema IN ('public', 'information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

• pg_catalog包含PostgreSQL所有内置系统表和视图
• information_schema提供标准化的元数据视图

内容的提问来源于stack exchange，提问作者Igor