The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.