表重命名/交换后如何清理Snowflake列默认值定义

在Snowflake中创建包含引用其他列默认值的表时，系统会将表名注入默认值定义。但执行表重命名或SWAP操作后，默认值仍保留原表名。我需要读取并清理默认值中的原库表名，但因默认值内表名与当前表名不匹配，难以实现。请问如何确定默认值定义中的原表名？原表名是否存在于information_schema视图中？

附创建表代码：

create or replace TABLE MY_DB.PUBLIC.TABLE_A_RENAMED cluster by (COLUMN7)(
COLUMN1 VARCHAR(16777216) NOT NULL DEFAULT CURRENT_USER(),
COLUMN7 NUMBER(38,0) NOT NULL,
COLUMN8 VARCHAR(16777216) NOT NULL DEFAULT TABLE_A_ORIGINAL.COLUMN1 || '_' || (CAST(TABLE_A_ORIGINAL.COLUMN7 AS VARCHAR(16777216)))
);

问题解答 #

• 原表名是否存在于information_schema视图中？
  答案是肯定的，但不会单独存储为独立字段，而是嵌入在INFORMATION_SCHEMA.COLUMNS的COLUMN_DEFAULT字段的SQL表达式里。

• 如何提取默认值中的原表名？
  你需要解析COLUMN_DEFAULT的字符串内容，用正则或字符串函数提取表名：

  1. 先查询获取目标列的默认值表达式：

     SELECT COLUMN_NAME, COLUMN_DEFAULT
     FROM MY_DB.INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'PUBLIC'
       AND TABLE_NAME = 'TABLE_A_RENAMED'
       AND COLUMN_NAME = 'COLUMN8';
     

  2. 用正则提取原表名，比如针对你的场景，匹配列名前的表标识符：

     SELECT 
       COLUMN_NAME,
       COLUMN_DEFAULT,
       REGEXP_SUBSTR(COLUMN_DEFAULT, '([A-Z_]+)\.COLUMN1', 1, 1, 'i', 1) AS ORIGINAL_TABLE_NAME
     FROM MY_DB.INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'PUBLIC'
       AND TABLE_NAME = 'TABLE_A_RENAMED'
       AND COLUMN_NAME = 'COLUMN8';
     

     这个正则会精准提取出TABLE_A_ORIGINAL。

• 清理默认值中的原表名
  提取原表名后，用正则替换生成ALTER语句，执行即可更新默认值：

  SELECT 
    CONCAT(
      'ALTER TABLE MY_DB.PUBLIC.TABLE_A_RENAMED ALTER COLUMN COLUMN8 SET DEFAULT ',
      REGEXP_REPLACE(COLUMN_DEFAULT, 'TABLE_A_ORIGINAL\.', 'TABLE_A_RENAMED\.')
    ) AS ALTER_STATEMENT
  FROM MY_DB.INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'PUBLIC'
    AND TABLE_NAME = 'TABLE_A_RENAMED'
    AND COLUMN_NAME = 'COLUMN8';
  

  复制生成的ALTER语句执行，就能把默认值里的原表名替换成当前表名。

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