获取Snowflake数据库中各类对象统计数量的SQL查询

以下SQL语句可统计Snowflake数据库内各架构（Schema）下表、视图、存储过程的数量：

WITH schema_objects AS (
    SELECT 
        TABLE_SCHEMA AS schemaname,
        'TABLE' AS object_type,
        COUNT(*) AS object_count
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    GROUP BY TABLE_SCHEMA
    UNION ALL
    SELECT 
        TABLE_SCHEMA AS schemaname,
        'VIEW' AS object_type,
        COUNT(*) AS object_count
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'VIEW'
    GROUP BY TABLE_SCHEMA
    UNION ALL
    SELECT 
        ROUTINE_SCHEMA AS schemaname,
        'PROCEDURE' AS object_type,
        COUNT(*) AS object_count
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE'
    GROUP BY ROUTINE_SCHEMA
)
SELECT 
    schemaname,
    COALESCE(MAX(CASE WHEN object_type = 'TABLE' THEN object_count END), 0) AS tablesperschema,
    COALESCE(MAX(CASE WHEN object_type = 'VIEW' THEN object_count END), 0) AS viewsperschema,
    COALESCE(MAX(CASE WHEN object_type = 'PROCEDURE' THEN object_count END), 0) AS procedures
FROM schema_objects
GROUP BY schemaname
ORDER BY schemaname;

输出示例 #

SchemaName   TablesPerSchema ViewsPerSchema Procedures
------------ --------------- -------------- -----------
abcd         46              117            163
GDS          0               3              3
IATA         3               10             13
Integration  2               4              6

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