如何使用SQL查询表名、schema所有者、表大小及引用该表的对象

以下代码均可以直接修改过滤条件中的schema名称后执行，返回结果完全覆盖你需要的表名、模式所有者、引用对象、空间占用四个维度的信息。

PostgreSQL 版本 #

SELECT 
    c.relname AS tablename,
    u.usename AS schema_owner,
    STRING_AGG(DISTINCT dep.objid::regclass::text, ', ') AS referenced_by_objects,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS table_total_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_user u ON n.nspowner = u.usesysid
LEFT JOIN 
    pg_depend dep ON dep.refobjid = c.oid AND dep.deptype = 'n'
WHERE 
    n.nspname = 'your_schema_name' -- 替换为实际schema名称
    AND c.relkind = 'r' -- 仅查询普通表，需要包含视图/索引可修改该过滤条件
GROUP BY 
    c.relname, u.usename, c.oid
ORDER BY 
    pg_total_relation_size(c.oid) DESC;

• referenced_by_objects字段会返回所有引用该表的视图、存储过程、外键等对象，无引用时返回null
• table_total_size默认返回表+索引的总占用大小，仅需要表本身大小可将pg_total_relation_size替换为pg_relation_size

MySQL 版本（适用于MySQL 5.7及以上） #

SELECT 
    t.TABLE_NAME AS tablename,
    t.TABLE_SCHEMA AS schema_name,
    CONCAT_WS(' | ',
        (SELECT GROUP_CONCAT(DISTINCT CONCAT('外键@',TABLE_NAME, '.', CONSTRAINT_NAME) SEPARATOR ', ') 
         FROM information_schema.KEY_COLUMN_USAGE 
         WHERE REFERENCED_TABLE_SCHEMA = t.TABLE_SCHEMA 
           AND REFERENCED_TABLE_NAME = t.TABLE_NAME),
        (SELECT GROUP_CONCAT(DISTINCT CONCAT('视图@',TABLE_NAME) SEPARATOR ', ') 
         FROM information_schema.VIEWS 
         WHERE TABLE_SCHEMA = t.TABLE_SCHEMA 
           AND VIEW_DEFINITION LIKE CONCAT('%', t.TABLE_NAME, '%')),
        (SELECT GROUP_CONCAT(DISTINCT CONCAT('存储过程@',ROUTINE_NAME) SEPARATOR ', ') 
         FROM information_schema.ROUTINES 
         WHERE ROUTINE_SCHEMA = t.TABLE_SCHEMA 
           AND ROUTINE_DEFINITION LIKE CONCAT('%', t.TABLE_NAME, '%'))
    ) AS referenced_by_objects,
    CONCAT(ROUND((t.DATA_LENGTH + t.INDEX_LENGTH)/1024/1024, 2), ' MB') AS table_total_size
FROM 
    information_schema.TABLES t
WHERE 
    t.TABLE_SCHEMA = 'your_schema_name' -- 替换为实际schema名称
    AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY 
    (t.DATA_LENGTH + t.INDEX_LENGTH) DESC;

• MySQL无统一依赖查询视图，因此将不同类型的引用对象做了分类标记，无对应引用时该分类位置返回空
• 如需单独获取表数据大小、索引大小，可以分别取DATA_LENGTH、INDEX_LENGTH字段计算

Oracle 版本 #

SELECT 
    t.table_name AS tablename,
    t.owner AS schema_owner,
    LISTAGG(DISTINCT CONCAT(d.type, '@', d.name), ', ') WITHIN GROUP (ORDER BY d.name) AS referenced_by_objects,
    CONCAT(ROUND(s.bytes/1024/1024, 2), ' MB') AS table_total_size
FROM 
    all_tables t
LEFT JOIN 
    all_dependencies d ON d.referenced_name = t.table_name 
        AND d.referenced_owner = t.owner 
        AND d.owner != t.owner
LEFT JOIN 
    all_segments s ON s.segment_name = t.table_name 
        AND s.owner = t.owner 
        AND s.segment_type = 'TABLE'
WHERE 
    t.owner = 'YOUR_SCHEMA_NAME' -- 替换为实际schema名称，Oracle默认大写
GROUP BY 
    t.table_name, t.owner, s.bytes
ORDER BY 
    s.bytes DESC NULLS LAST;

• 当前用户无DBA权限时使用all_前缀的系统视图即可，有DBA权限可以替换为dba_前缀查询全实例的表信息
• 查询结果已经自动排除了表自身的内部依赖

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