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!
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');
Some extra thoughts that might be helpful to solve your problem.