1 0
Read Time:1 Minute, 40 Second

Consider a scenario when business wants to clean up the database for a particular requirement. Clean up here we mean drop all the tables lies inside the database. At first glance it seems pretty straightforward and we can issue the DROP table command to remove all the tables. But Business has put one condition here that if any table is part of VIEW then no need to drop the table. In other words there are VIEWS available in the database based on the certain Base tables. So before dropping the tables blindly we need to ensure that there should not be an underlying table for any VIEW.

Also Business wants before executing the DROP commands on legitimate tables , can we capture the table name along with syntax in an output for cross verification. Once the business validated that outcome of procedure is fine then we can execute the DROP statement process.

So first we have to traverse the list of all tables present in our database. Then we need to check in the OBJECT_DEPENDENCIES view to get the list of dependent views on the tables.

Below stored procedure we have written to implement the logic .

Drop Proc

Now call the procedure with Database name and Schema as inpuit argument.

CALL DROP_TABLE_CHECK(‘demo_db’, ‘public’);

Below is the output of the procedure.

Procedure has captured the list of tables needs to be drop. Also we have maintained an array to capture the list of VIEWS based on the underlying table.

DROP PROC Output1
DROP PROC Output2

Once we receive the confirmation we can uncomment the below part from procedure and execute the code. This will drop the tables programmatically from the database.

//var obj_query_statement = snowflake.createStatement( {sqlText: obj_query} );
//var objrs = obj_query_statement.execute();
//while (objrs.next())
// {
//var dbname = objrs.getColumnValue(1);
//}

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *