How to Drop All Tables in BigQuery

How can I drop all tables in a BigQuery dataset at once?

DROP TABLE removes one or more tables from a BigQuery dataset, freeing storage and metadata.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I quickly remove every table in a dataset?

Loop through INFORMATION_SCHEMA.TABLES (or __TABLES_SUMMARY__) to build a comma-separated DROP TABLE list, then execute it with EXECUTE IMMEDIATE. BigQuery doesn’t support wildcards like DROP TABLE dataset.*.

What is the recommended scripting pattern?

Create a BigQuery script that (1) assembles the DDL string, (2) optionally verifies the list, and (3) runs the statement in a single transaction. Add IF EXISTS to avoid errors.

Can I keep specific tables?

Yes. Filter out rows in INFORMATION_SCHEMA.TABLES by table_name NOT IN ('Customers') or by older than a given creation time.

Step-by-step script

1. DECLARE a string variable.
2. SELECT STRING_AGG('DROP TABLE IF EXISTS `' || table_schema || '.' || table_name || '`;') AS ddl FROM INFORMATION_SCHEMA.TABLES.
3. EXECUTE IMMEDIATE ddl.

Why add IF EXISTS?

IF EXISTS prevents your script from failing if another process already removed a table between DDL generation and execution.

How can I audit the drop?

PRINT the generated DDL or insert it into an audit table before execution. BigQuery Job History also records all DDL jobs.

Why How to Drop All Tables in BigQuery is important

How to Drop All Tables in BigQuery Example Usage


--Remove every ecommerce table in dataset "sales"
declare ddl string;
set ddl = (
  select string_agg(
    format('DROP TABLE IF EXISTS `ecom.sales.%s`;', table_name), '\n'
  )
  from `ecom`.sales.INFORMATION_SCHEMA.TABLES
  where table_name in ('Customers','Orders','Products','OrderItems')
);
execute immediate ddl;

How to Drop All Tables in BigQuery Syntax


DROP TABLE [IF EXISTS] `project.dataset.table1` [, `project.dataset.table2`, ...];

--Dynamic version for all tables in a dataset
declare ddl string;
set ddl = (
  select string_agg(
    format('DROP TABLE IF EXISTS `myproj.myds.%s`;', table_name), '\n'
  )
  from `myproj`.myds.INFORMATION_SCHEMA.TABLES
);
execute immediate ddl;

Common Mistakes

Running DROP TABLE per table manually—time-consuming and error-prone. Fix it by generating one DDL string with STRING_AGG and executing it once.
Omitting IF EXISTS and breaking the script when a table is concurrently deleted. Always add IF EXISTS to each DROP statement.