Yes, it's possible. Use the
DROP TABLE SQL syntax to drop one or more tables in one execution. Use Panoply's Workbench tool or any third-party SQL client tool you're familiar with to run the statement.
Before you drop tables, ensure that you know the team/role you belong to. The team you are in determines what you can do to a table, plus, what other actions you can do in Panoply.
To drop a single table, run the following SQL statement (where
table1 is your table's name and
schema1 is your schema's name):
DROP TABLE "schema1"."table1";
To drop several tables:
DROP TABLE "schema1"."table1", "schema1"."table2", "schema2"."table3";
Use SQL to retrieve table names
The following queries can help you get the names of the tables you want to drop, and also help you build the correct
DROP TABLE statement to execute.
- To generate a list of all tables that match some prefix, you can run the query below. Specify your own prefix and your target schema.
SELECT DISTINCT tablename FROM pg_tables WHERE tablename LIKE 'some_prefix%' AND schemaname = 'public';
The query above is useful in getting a list of tables that belong to a specific data source. Most data sources in Panoply, like Shopify, HubSpot, and Instagram, use a prefix as a convention in naming their tables.
- To get a list of all tables in the
publicschema, with each table wrapped with literal quotes and separated by commas:
SELECT '"' + schemaname + '"."' + tablename + '",' AS "DROP TABLE" FROM pg_tables WHERE schemaname = 'public';
You can use this as an SQL template for dropping multiple tables. Modify it to fit your needs. The next section provides a detailed example of using this statement.
Example use case
The following instructions demonstrate how to drop multiple Intercom tables ingested by Stitch.
- Run the following SQL query:
SELECT DISTINCT '"' + schemaname + '"."' + "tablename" +'",' AS "DROP TABLE" FROM pg_tables WHERE schemaname = 'stitch_intercom';
Copy the results, including the header and paste it in your text editor.
Edit the copied text as needed and replace the last comma with a semicolon. The final statement should look like this:
- Run the statement to drop the tables.
Note that you'll get an error if there are dependencies on these tables, such as views you may have built. We suggest identifying the dependencies of your tables first before you consider deleting them.