How to List Tables in BigQuery

What is the fastest way to list tables in BigQuery?

Run a simple SELECT against the INFORMATION_SCHEMA.TABLES view or use the bq ls CLI command. Both return table names instantly without scanning data.

Which SQL syntax lists tables in a dataset?

Use SELECT table_name FROM `project_id.dataset_id.INFORMATION_SCHEMA.TABLES` WHERE table_type = 'BASE TABLE';. Replace project_id and dataset_id with your own values.

Can I include views and materialized views?

Yes. Omit the table_type filter or adjust it to 'VIEW' or 'MATERIALIZED VIEW' to retrieve those object types.

How do I list tables with row counts and sizes?

Add row_count and size_bytes columns from INFORMATION_SCHEMA.TABLE_STORAGE. Join them on table_name for richer metadata without full table scans.

Why use INFORMATION_SCHEMA over bq ls?

SQL is portable and works inside workflows, while bq ls is convenient in terminals and scripts. INFORMATION_SCHEMA also lets you filter, sort, or join for advanced reporting.

Best practice: reference tables by full path

Always qualify tables with project and dataset (project.dataset.table). This avoids ambiguity and cross-region errors, especially in multi-project environments.

Example: audit ecommerce tables

The query below lists all base tables in ecommerce dataset, then joins storage metadata to display row counts and sizes.

WITH tables AS (
SELECT table_name
FROM `myproj.ecommerce.INFORMATION_SCHEMA.TABLES`
WHERE table_type = 'BASE TABLE'
)
SELECT t.table_name,
s.row_count,
ROUND(s.size_bytes/1024/1024, 2) AS size_mb
FROM tables t
JOIN `myproj.ecommerce.__TABLES__` s
ON t.table_name = s.table_id
ORDER BY size_mb DESC;


How do I script this in the CLI?

bq ls --project_id=myproj --format=prettyjson ecommerce

The --format flag outputs machine-readable JSON for automation.