How to List Databases in BigQuery

Galaxy Glossary

How do I list all databases in BigQuery?

Listing databases in BigQuery means returning every dataset inside a project with either SQL or the bq CLI.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why would I need to list databases (datasets) in BigQuery?

Listing datasets helps you audit existing data, verify new dataset creation, and confirm access before querying tables like Customers or Orders.

What is the fastest way to list all datasets?

Run bq ls from your terminal. It shows every dataset you can access in the active project within seconds.

How do I list datasets with SQL?

Query the INFORMATION_SCHEMA.SCHEMATA view in the desired region. You can filter, sort, or join the results just like any other table.

Can I filter datasets by name or label?

Yes. Both the CLI and SQL approaches support filtering. In SQL, add a WHERE clause; in the CLI, use --filter.

Which permissions are required?

You need at least the bigquery.datasets.list permission, granted via roles like roles/bigquery.user or stronger.

Best practice: save your default project

Set bq --project_id in your shell profile or use the CLOUDSDK_CORE_PROJECT environment variable to avoid repetitive flags.

Best practice: scope SQL to a region

Always reference the region (e.g., region-us) when using INFORMATION_SCHEMA. This prevents cross-region errors and speeds up metadata scans.

Best practice: script repeated audits

Automate dataset audits with scheduled queries or cron jobs that export the list to a table or Cloud Storage for version control.

Why How to List Databases in BigQuery is important

How to List Databases in BigQuery Example Usage


-- Confirm the project contains the datasets that host your ecommerce tables
SELECT schema_name AS dataset
FROM `shop_galaxy.region-us.INFORMATION_SCHEMA.SCHEMATA`
WHERE schema_name LIKE '%sales%'
ORDER BY dataset;

How to List Databases in BigQuery Syntax


CLI Syntax:

bq ls [--project_id=<PROJECT_ID>] [--filter="labels.key:value"] [--format=prettyjson | pretty | csv]

SQL Syntax:

SELECT schema_name AS dataset_name
FROM `<PROJECT_ID>.<REGION>.INFORMATION_SCHEMA.SCHEMATA`
[WHERE schema_name LIKE '%pattern%']
[ORDER BY dataset_name];

Example with ecommerce context (project "shop_galaxy"):

SELECT schema_name
FROM `shop_galaxy.region-us.INFORMATION_SCHEMA.SCHEMATA`
WHERE schema_name IN ('sales', 'marketing');

Common Mistakes

Omitting the region in INFORMATION_SCHEMA queries. BigQuery returns an error like "Access Denied: Table region-us.INFORMATION_SCHEMA.SCHEMATA not found." Fix by adding the correct region prefix.
Running bq ls without setting the project, resulting in an empty list. Fix by adding --project_id=<PROJECT_ID> or setting CLOUDSDK_CORE_PROJECT.