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

Jim_Knicely

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 Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/DroppingTables.htm

Have fun!