snowflake convert all columns to uppercase

Question

I have SP which looks at Info schema columns table and converts lower case columns to uppercase. Not able to wrap double codes around object.

ALTER TABLE SCHEMA_S.TBLNAME RENAME COLUMN ColName to COLNAME; //Won't work
ALTER TABLE "SCHEMA_S"."TBLNAME" RENAME COLUMN "ColName" to "COLNAME"; //this works

  select ('ALTER TABLE ' || TABLE_NAME || ' RENAME COLUMN "' || COLUMN_NAME || '" to "' || 
  UPPER(COLUMN_NAME) || '";') AS COL1 
  from information_schema.columns 
  WHERE TABLE_NAME = 'TBLNAME' AND TABLE_SCHEMA = 'SCHEMA_S'
  AND COLUMN_NAME != UPPER(COLUMN_NAME)  //this works in Snowflake

But within javascript, not able to use double quotes javascript

    var table_control = " SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.' ,TABLE_NAME, ' RENAME COLUMN ', COLUMN_NAME, ' to ', UPPER(COLUMN_NAME), ';') AS COL1 " 
        table_control += "  FROM INFORMATION_SCHEMA.COLUMNS "
        table_control += " WHERE TABLE_SCHEMA = " + String.fromCharCode(39)  + CURRENT_SCHEMA + String.fromCharCode(39)
        table_control += "   AND TABLE_NAME   = " + String.fromCharCode(39)  + CURRENT_TABLE + String.fromCharCode(39)
        table_control += "   AND COLUMN_NAME != UPPER(COLUMN_NAME) " ; //this works

    var table_control = " SELECT CONCAT('ALTER TABLE "', TABLE_SCHEMA,'"."' ,TABLE_NAME, '" RENAME COLUMN "', COLUMN_NAME, '" to "', UPPER(COLUMN_NAME), '";') AS COL1 " 
        table_control += "  FROM INFORMATION_SCHEMA.COLUMNS "
        table_control += " WHERE TABLE_SCHEMA = " + String.fromCharCode(39)  + CURRENT_SCHEMA + String.fromCharCode(39)
        table_control += "   AND TABLE_NAME   = " + String.fromCharCode(39)  + CURRENT_TABLE + String.fromCharCode(39)
        table_control += "   AND COLUMN_NAME != UPPER(COLUMN_NAME) " ; //won't work

I get below, JavaScript compilation error: Uncaught SyntaxError: Unexpected string in CONVERT_TBL_COLUMNS_UPPERCASE at ' var table_control = " SELECT CONCAT('ALTER TABLE "', TABLE_SCHEMA,'"."' ,TABLE_NAME, '" RENAME COLUMN "', COLUMN_NAME, '" to "', UPPER(COLUMN_NAME), '";') AS COL1 " ' position 58

Bottom line - Need to concatenate objects(columns/schema/table) with double check so that i can rename column name uppercase dynamically thru javascript.

Please help, I'm missing something simple!

Does this answer your question? [Escape quotes in JavaScript](https://stackoverflow.com/questions/2004168/escape-quotes-in-javascript) — Jon P, Nov 23 '21 at 04:46

score 3 · Accepted Answer · answered Nov 23 '21 at 05:33

I created a JS UDF that will rename columns to uppercase.

It uses JS template strings to avoid problems with escaping quotes:

create or replace procedure alter_table_to_uppercase(
    TABLE_SCHEMA string,
    TABLE_NAME string
) returns variant
language javascript as $$

var select_cols = `
    select column_name
    from information_schema.columns
    where table_catalog = current_database()
    and table_schema = '${TABLE_SCHEMA}'
    and table_name = '${TABLE_NAME}'
    and column_name != upper(column_name)
`;

var statement1 = snowflake.createStatement( {sqlText: select_cols} );
var result_set1 = statement1.execute();

var cols_changed = [];

while (result_set1.next())  {
    var col_name = result_set1.getColumnValue(1);
    var alter_sql = `
        alter table ${TABLE_SCHEMA}.${TABLE_NAME}
        rename column "${col_name}" to "${col_name.toUpperCase()}"
    `
    snowflake.createStatement({sqlText: alter_sql}).execute();
    
    cols_changed.push(col_name);
}

return cols_changed;
$$

Testing it is easy:

create or replace table lowercased
as select 1 "a", 2 "b", 3 "c"
;

call alter_table_to_uppercase(current_schema(), 'LOWERCASED');

Recommend predicate removing columns forming part of clustering key to prevent failures. — Adrian White, Nov 23 '21 at 06:00

Adrian White · Answer 2 · 2021-11-23T06:45:31.647

Some extra thoughts that might be helpful to solve your problem.

You cannot rename a column that is part of a clustering key. Meaning the query/stored proc will fail when it hit a column that's a clustering key.
You can identify columns forming part of the clustering key by using the System$Clustering_Information
By default all column names are stored in uppercase if not in quotes So you're not really having to do anything. Adding quotes removes this default setting.
You might be better solving the problem with a Session parameter like QUOTED_IDENTIFIERS_IGNORE_CASE

snowflake convert all columns to uppercase

2 Answers2