Tip: Drop All Tables/Sequences from a User

I was testing a deployment script today and after about 10 tables something would fail. And it took a few goes to get it right (silly leading spaces). So I was getting very frustrated manually dropping all tables and constraints I had just built. So I did a bit of digging and came across this:

select
'DROP TABLE [SCHEMA_NAME].' || TABLE_NAME || ' CASCADE CONSTRAINTS;'
from
ALL_TABLES
where
TABLE_NAME like 'APX_%' and OWNER = 'OWNER_NAME'

This will create a SQL statement for each table on separate rows, allowing you to copy and paste the result into SQL Dev and run it as a script. [SCHEMA_NAME] is the name of the user you want to drop from. E.g.

DROP TABLE USERS.TBL_USERS_DETAILS CASCADE CONSTRAINTS;
DROP TABLE USERS.TBL_USERS_PERMISSIONS CASCADE CONSTRAINTS;

Cascade Constraints will drop all constraints that reference this table. Without it, if another table references the table it will not let you drop it.

If you want only the tables in your user it can be as simple as:

select
'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS;'
from
USER_TABLES

Be careful when doing this as you don’t want to drop tables by accident. I only use this in our development environment. I wouldn’t ever use this in production.
 
So this was very useful, except for whenever I ran the script again the creation of sequences would fail… Because they already exist, duh! So I ran this script to generate the DROP SEQUENCE statements and ran it alongside dropping the tables.

select
'DROP SEQUENCE [SCHEMA_NAME].' || SEQUENCE_NAME
from
ALL_SEQUENCES
where
SEQUENCE_OWNER = '[SCHEMA_NAME]'

Works in the exact same way as the table script above. This saved me a lot of time debugging my deployment script, and hopefully it can help others.

I know it would be possible to insert this script into PL/SQL to generate and then run the table deletion script, but I would advise against it. You should always make sure the tables you are dropping are correct before running this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s