0

I have a list of all column names to be dropped from multiple tables in a schema, but not all the column names in the list have it's presence in all the tables.

For the schema and the table name I have can I in a single query do a check if the columns match the column names in the list just drop that

Say the column list to drop is

Drop_col = ['AB',' BC', FG', 'TR']

But my TABLE1 has only 'BC', 'TR' in the column list

Query =  ALTER TABLE SCHEMA_NAME.TABLE1 DROP COLUMN IN ({Drop}).format (Drop = Drop_col)

Something Like this?? or any other suggestions ??

Simon D
  • 5,730
  • 2
  • 17
  • 31
Manak
  • 27
  • 2
  • 8

1 Answers1

0

You can use COLUMNS view and Stored Procedure to achieve this.

1- Pass the list of columns in stored procedure

2- Using COLUMNS view Get the list of table, column results

3-Iterate on the resultset and drop columns

https://docs.snowflake.com/en/sql-reference/info-schema/columns.html

https://community.snowflake.com/s/article/How-to-scan-Result-set-in-stored-procedures

https://community.snowflake.com/s/article/Select-columns-based-on-condition-in-Snowflake-using-Information-Schema-and-Stored-Procedure

Iqra Ijaz
  • 261
  • 1
  • 3