Snowflake SQL查询优化：为COLUMN_DETAILS最后一列添加VARCHAR

问题核心：当前查询通过LISTAGG生成列定义字符串时，最后一列未附带VARCHAR标识，导致无法直接用于调用存储过程创建表。

修改后的完整SQL #

USE ROLE ROLENAME;
SET SemanticLayerSchemaDetails = '''DB_D''';
SET TableSchemaDetails = 'SCHEMA_D';
SET TableNamesArray = '[''TABLE_SALES'']';

with CTE_SchemaOneObjectDetails
AS
(
SELECT 
$SemanticLayerSchemaDetails AS SCHEMA_D, (''''|| TABLE_CATALOG || '''') AS DB_D, 
(''''|| TABLE_NAME || '''') AS RLOBJ,
-- 调整LISTAGG逻辑：先给每个列添加VARCHAR，再用逗号分隔
listagg('"'||COLUMN_NAME||'" VARCHAR', ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION ASC) AS COLUMN_DETAILS
from INFORMATION_SCHEMA.columns 
where TABLE_SCHEMA = $TableSchemaDetails
and table_name IN (select value from table(flatten ( input => parse_json($TableNamesArray))))
GROUP BY Table_Name,TABLE_CATALOG,TABLE_SCHEMA
  )

SELECT * FROM CTE_SchemaOneObjectDetails

逻辑说明 #

原写法listagg('"'||COLUMN_NAME||'"', ' VARCHAR , ')是将VARCHAR ,作为分隔符，这意味着只有前N-1列的末尾会带上VARCHAR，最后一列没有对应的分隔符，自然缺失VARCHAR标识。

调整后改为先给每个列名拼接 VARCHAR，再用, 作为分隔符，这样所有列都会带上VARCHAR属性，生成的COLUMN_DETAILS字符串可以直接用于CREATE TABLE语句的列定义部分。

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