求助：如何用SnowSQL获取Snowflake数据库及下属对象详情并生成CSV

一、编写查询获取全量对象信息 #

可以通过查询Snowflake的系统视图，把数据库、模式、表、视图、阶段、管道等对象的信息整合到一个结果集里。下面是包含常用关键字段的示例查询：

-- 数据库信息
SELECT
    DATABASE_NAME AS object_database,
    NULL AS object_schema,
    DATABASE_NAME AS object_name,
    'DATABASE' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.DATABASES
WHERE IS_TRANSIENT = 'NO' -- 可选：过滤临时数据库
UNION ALL
-- 模式信息
SELECT
    DATABASE_NAME AS object_database,
    SCHEMA_NAME AS object_schema,
    SCHEMA_NAME AS object_name,
    'SCHEMA' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE IS_TRANSIENT = 'NO' -- 可选：过滤临时模式
UNION ALL
-- 表信息
SELECT
    TABLE_CATALOG AS object_database,
    TABLE_SCHEMA AS object_schema,
    TABLE_NAME AS object_name,
    'TABLE' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
-- 视图信息
SELECT
    TABLE_CATALOG AS object_database,
    TABLE_SCHEMA AS object_schema,
    TABLE_NAME AS object_name,
    'VIEW' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.VIEWS
UNION ALL
-- 内部阶段信息
SELECT
    TABLE_CATALOG AS object_database,
    TABLE_SCHEMA AS object_schema,
    STAGE_NAME AS object_name,
    'INTERNAL STAGE' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.STAGES
WHERE STAGE_TYPE = 'INTERNAL'
UNION ALL
-- 外部阶段信息
SELECT
    TABLE_CATALOG AS object_database,
    TABLE_SCHEMA AS object_schema,
    STAGE_NAME AS object_name,
    'EXTERNAL STAGE' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.STAGES
WHERE STAGE_TYPE = 'EXTERNAL'
UNION ALL
-- 管道信息
SELECT
    PIPE_CATALOG AS object_database,
    PIPE_SCHEMA AS object_schema,
    PIPE_NAME AS object_name,
    'PIPE' AS object_type,
    CREATED,
    OWNER
FROM INFORMATION_SCHEMA.PIPES
ORDER BY object_database, object_schema, object_type, object_name;

• 要是需要更多字段，比如表的行数ROW_COUNT、阶段的存储路径STAGE_LOCATION、管道状态PIPE_STATE，直接从对应的系统视图里加就行。
• 想包含临时对象的话，删掉WHERE IS_TRANSIENT = 'NO'这行过滤条件。

二、用SnowSQL导出为CSV #

方法1：直接通过命令行参数导出 #

适合小体量的结果集：

1. 打开终端，登录SnowSQL：

snowsql -a <你的账户名> -u <你的用户名> -d <默认数据库> -s <默认模式>

2. 在SnowSQL会话里设置导出参数并执行查询：

!set output_file=./snowflake_objects_details.csv
!set format=csv
!set header=true
-- 粘贴上面的联合查询语句并执行

执行完后，CSV文件会出现在你当前的终端工作目录里。

方法2：用COPY INTO导出到阶段再下载 #

适合大体积的结果集，稳定性更好：

1. 先创建一个临时内部阶段（没有的话）：

CREATE OR REPLACE TEMPORARY STAGE object_details_stage;

2. 把查询结果导出到这个阶段：

COPY INTO @object_details_stage/objects_details.csv
FROM (
    -- 粘贴上面的联合查询语句
)
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' HEADER = TRUE);

3. 把阶段里的文件下载到本地：

snowsql -a <你的账户名> -u <你的用户名> -q "GET @object_details_stage/objects_details.csv ./;"

注意事项 #

• 确保你的Snowflake账号有足够权限，比如对目标数据库、模式的USAGE权限，以及系统视图的SELECT权限。
• 要查所有数据库的对象，得切换到ACCOUNTADMIN角色或者有跨数据库查询权限的角色。

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