Warning
If this process isn't carried out carefully, there's a risk that every table that your account has access to will be permanently deleted.
Supermetrics enables you to bring all your marketing data into BigQuery with just a few clicks. We shard the data by date to maximize performance, which means that dropping a table isn't quite as simple as it would otherwise be. If you find that for any reason you need to drop a table (all shards), follow the steps below.
This guide describes how to delete multiple BigQuery tables using a wildcard.
Instructions
- Copy this SQL:
SELECT CONCAT("drop table ",table_schema,".", table_name, ";" )FROM <insert_your_dataset_name>.INFORMATION_SCHEMA.TABLES
WHERE table_name like "INSERT_YOUR_TABLE_NAME_%"
ORDER BY table_name DESC
- Open BigQuery. Click SQL QUERY and paste in the code from step 1.
- Replace <insert_your_dataset_name> with your dataset name.
- Replace INSERT_YOUR_TABLE_NAME_% with your table name. If you want to delete multiple tables starting with certain table name, then only replace the part before the wildcard %.
- Click Run.
- Click Save results. This will open the dropdown menu to select the format and destination to save the results.
- Select CSV (local file) from the dropdown menu.
- Open your exported CSV file, and copy all columns from row 2 to the last row. Each row should contain the content such as "drop table Supermetrics_test.FBADS_AD_20250115;"
- Go back to BigQuery and add /* at the beginning of the code copied from step 1 and add */ at the end to comment it out.
- Paste the drop statements below the code.
- Review your SQL carefully. As mentioned earlier, it's crucial to proceed with caution during this process. If not executed correctly, there is a potential risk of permanently deleting all tables your account has access to.
- Run the query to delete the specified tables.