Database Management
How to Quick Drop Multiple Tables
occasionally, there is a need to quickly drop a list of tables and you don’t always want to write or generate each command individually in Aginity. So, here is a quick example of how you can use a ‘Select’ SQL statement to generate a list of drop commands for you. Now, this approach assumes there is a common naming convention, so, you may need to adapt it to your needs.
An outline of the Drop Multiple Tables Process
Here is a quick summary of the steps to generate the drop statements from _V_Table:
- Build required Netezza SQL select; paying particular attention to the where clause criteria to exclude any unnecessary tables.
- Execute the SQL statement
- Copy from Aginity Results Tab without headers
- Past into new Aginity Query window
- validate that only the tables are in the list — No extras
- Click with the SQL Drop command list and Execute as a single batch
Example generate the drop statements
select ‘Drop table ‘||tablename||’;’
from _V_TABLE
where tablename like ‘NZCC_TT_%’;
Related References
- Database – What is DDL?
- Database – What is DCL?
- Database – What is DML?
- Database – What is TCL?
- Netezza / PureData – How to add multiple columns to a Netezza table in one SQL
- Database Table Field Ordering Effective Practices
- Structured Query Language (SQL) Tuning
