A Snowflake table has been populated with 2000+ columns due to each property being sent as a KEY instead of VALUE.
Need to drop these columns.
All of them begin with _ e.g. '_ASD3234E2
To test my syntax will be correct for the final ALTER TABLE command, I'm first trying a SHOW COLUMNS command.
SHOW COLUMNS LIKE '_%' IN TABLE DELIVERY_VIEW_DETAIL;
This is showing all columns in the table still though.
As @Austin mentioned as well, it needs to be escaped -
SHOW COLUMNS LIKE '\\_%' IN TABLE TRANS_1 ;
+------------+-------------+-------------+
| table_name | schema_name | column_name |
|------------+-------------+-------------+
| TRANS_1 | PUBLIC | _COL1 |
| TRANS_1 | PUBLIC | _COL2 |
| TRANS_1 | PUBLIC | _COL3 |
| TRANS_1 | PUBLIC | _COL4 |
+------------+-------------+-------------+
Without escape -
SHOW COLUMNS LIKE '_%' IN TABLE TRANS_1 ;
+------------+-------------+-------------+
| table_name | schema_name | column_name |
|------------+-------------+-------------+
| TRANS_1 | PUBLIC | ID |
| TRANS_1 | PUBLIC | _COL1 |
| TRANS_1 | PUBLIC | _COL2 |
| TRANS_1 | PUBLIC | _COL3 |
| TRANS_1 | PUBLIC | _COL4 |
+------------+-------------+-------------+
Single escape -
SHOW COLUMNS LIKE '\_%' IN TABLE TRANS_1 ;
+------------+-------------+-------------+
| table_name | schema_name | column_name |
|------------+-------------+-------------+
| TRANS_1 | PUBLIC | ID |
| TRANS_1 | PUBLIC | _COL1 |
| TRANS_1 | PUBLIC | _COL2 |
| TRANS_1 | PUBLIC | _COL3 |
| TRANS_1 | PUBLIC | _COL4 |
+------------+-------------+-------------+
All columns -
SHOW COLUMNS IN TABLE TRANS_1 ;
+------------+-------------+-------------+
| table_name | schema_name | column_name |
|------------+-------------+-------------+
| TRANS_1 | PUBLIC | ID |
| TRANS_1 | PUBLIC | _COL1 |
| TRANS_1 | PUBLIC | _COL2 |
| TRANS_1 | PUBLIC | _COL3 |
| TRANS_1 | PUBLIC | _COL4 |
+------------+-------------+-------------+