From Snowflake's documentation, it's clear how to add or overwrite a comment on a table column, but it doesn't look like you can remove a comment one without re-creating the table.
CREATE TABLE "My_Table" (
"my_column" INT
);
ALTER TABLE "My_Table" ALTER "my_column" COMMENT 'New Comment'; -- Works
ALTER TABLE "My_Table" ALTER "my_column" COMMENT NULL; -- Fails
ALTER TABLE "My_Table" ALTER "my_column" COMMENT ''; -- Works, but prefer NULL
As shown above, the best I can find is to set the comment to ''
, but I would really prefer it to be null. Does anyone know how to remove a comment?
It is possible to remove comment with ALTER TABLE ... UNSET COMMENT clause:
CREATE TABLE my_table (my_column INT);
ALTER TABLE my_table ALTER my_column COMMENT 'New Comment';
SELECT table_schema, table_name, column_name, comment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ILIKE 'my_table';
/*
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COMMENT
PUBLIC MY_TABLE MY_COLUMN New Comment
*/
ALTER TABLE my_table ALTER my_column UNSET COMMENT;
SELECT table_schema, table_name, column_name, comment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ILIKE 'my_table';
/*
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COMMENT
PUBLIC MY_TABLE MY_COLUMN
*/
Output: