Skip to content

Generate Drop Table Statements (drop_table.gen)

What This Script Does

This generator script creates DROP TABLE statements with CASCADE CONSTRAINTS and PURGE options for tables matching the specified criteria. It safely excludes system schemas and temporary tables.

The Script

rem  drop_table.gen
rem
select 'drop table ' || owner || '.' || table_name || ' cascade constraints purge;'
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table_name')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
   and temporary = 'N'
 order by owner, table_name
/

Usage

-- Basic usage
@drop_table.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&table_name - Table name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES

Sample Output

drop table HR.EMPLOYEES_BACKUP cascade constraints purge;
drop table HR.DEPARTMENTS_OLD cascade constraints purge;
drop table SCOTT.TEST_TABLE cascade constraints purge;

Key Features

CASCADE CONSTRAINTS: Automatically drops referential integrity constraints
PURGE: Immediately releases space without going to recycle bin
System Schema Protection: Excludes critical system schemas
Temporary Table Exclusion: Skips temporary tables

Common Use Cases

Drop All Tables in Schema

@drop_table.gen
-- Enter: SCOTT for owner, % for table_name

Drop Tables with Specific Pattern

@drop_table.gen
-- Enter: % for owner, %_BACKUP for table_name

Truncate Table Generator - Generate TRUNCATE statements
Disable Constraints Generator - Disable constraints before operations