Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

Posted September 5, 2019 by Jim Knicely, Vertica Principal Solution Architect

hands sweeping into a dustpan

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me!

Example:

dbadmin=> \! vsql -Atc "SELECT 'DROP TABLE ' || table_name || ' CASCADE;' FROM tables WHERE table_schema = 'public' AND table_name ILIKE 'test\_%';"
DROP TABLE test_1 CASCADE;
DROP TABLE test_2 CASCADE;
DROP TABLE test_3 CASCADE;
DROP TABLE test_4 CASCADE;
DROP TABLE test_5 CASCADE;
DROP TABLE test_10 CASCADE;
DROP TABLE test_12 CASCADE;
DROP TABLE test_20 CASCADE;
DROP TABLE test_30 CASCADE;
dbadmin=> \! vsql -Atc "SELECT 'DROP TABLE ' || table_name || ' CASCADE;' FROM tables WHERE table_schema = 'public' AND table_name ILIKE 'test\_%';" | vsql -o /dev/null
dbadmin=> \! vsql -Atc "SELECT 'DROP TABLE ' || table_name || ' CASCADE;' FROM tables WHERE table_schema = 'public' AND table_name ILIKE 'test\_%';"

P.S. Be careful with this command! Make sure when building the DROP commands that your search criteria is accurate!

Helpful Link:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/DroppingTables.htm

Have fun!