Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ALTER_COLUMN Store Procedure for Snowflake
CREATE OR REPLACE PROCEDURE ALTER_COLUMN(TABLE_NAME VARCHAR, TABLE_SCHEMA VARCHAR, TABLE_COLUMN VARCHAR, NEW_VALUE VARCHAR) RETURNS STRING
LANGUAGE JAVASCRIPT AS
$$
var EXEC = (sql,...binds)=>snowflake.execute({sqlText:sql,binds:binds});
var cols = EXEC(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=UPPER('${TABLE_NAME}') AND TABLE_SCHEMA=UPPER('${TABLE_SCHEMA}')`);
var newSQL = `CREATE OR REPLACE TABLE ${TABLE_SCHEMA}.${TABLE_NAME} AS\n SELECT \n`;
var count = cols.getRowCount();
while (cols.next())
{
count--;
if (cols['COLUMN_NAME'] == TABLE_COLUMN)
newSQL += NEW_VALUE + "AS " + cols['COLUMN_NAME'];
else
newSQL += cols['COLUMN_NAME'];
if (count > 0) { newSQL += ",\n";}
}
newSQL+=`\n FROM ${TABLE_SCHEMA}.${TABLE_NAME}`;
EXEC(newSQL);
return `Column ${TABLE_COLUMN} change using expression ${NEW_VALUE}`;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment