Snowflake如何删除指定Schema下所有外键，可否使用CTE实现

实现方案 #

你可以通过SQL动态拼接的方式批量生成删除外键的命令，无需手动逐条处理，具体操作如下：

• 替换占位符：将下方查询语句中的SCHEMA_TO_DELETE_IN替换为你要操作的目标Schema名称
• 执行查询生成批量删除语句：运行如下SQL，会直接输出所有需要执行的外键删除命令：

SELECT CONCAT('ALTER TABLE `', foreign_schema, '`.`', foreign_table, '` DROP FOREIGN KEY `', foreign_constraint, '`;') AS drop_fk_command
FROM (
    SELECT 
        fk_tco.table_schema as foreign_schema,
        fk_tco.table_name as foreign_table,
        fk_tco.constraint_name as foreign_constraint
    FROM information_schema.referential_constraints rco
    JOIN information_schema.table_constraints fk_tco 
        ON fk_tco.constraint_name = rco.constraint_name
        AND fk_tco.constraint_schema = rco.constraint_schema
    JOIN information_schema.table_constraints pk_tco
        ON pk_tco.constraint_name = rco.unique_constraint_name
        AND pk_tco.constraint_schema = rco.unique_constraint_schema
    WHERE pk_tco.table_schema = 'SCHEMA_TO_DELETE_IN'     
    ORDER BY fk_tco.table_schema, fk_tco.table_name
) AS all_foreign_keys;

• 执行批量删除：复制上一步查询得到的所有结果语句，直接执行即可完成该Schema下全部外键的批量删除。

验证所有外键已删除 #

执行如下查询，若返回结果为空，则代表目标Schema下所有外键均已删除：

SELECT *
FROM information_schema.referential_constraints rco
JOIN information_schema.table_constraints fk_tco 
    ON fk_tco.constraint_name = rco.constraint_name
    AND fk_tco.constraint_schema = rco.constraint_schema
JOIN information_schema.table_constraints pk_tco
    ON pk_tco.constraint_name = rco.unique_constraint_name
    AND pk_tco.constraint_schema = rco.unique_constraint_schema
WHERE pk_tco.table_schema = 'SCHEMA_TO_DELETE_IN';

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