# INFORMATION_SCHEMA in BigQuery

> INFORMATION_SCHEMA views describe tables, columns, partitions, jobs, storage and more, queried with SQL. Each view is scoped to a dataset or a region, the name is case-sensitive, every query is billed at least 10 MB, and results are never cached.

Source: https://docs.querylab.io/bigquery/information-schema/

Updated: 2026-10-27

`INFORMATION_SCHEMA` is a set of read-only views that describe your BigQuery objects: datasets, tables, columns, partitions, views, routines, jobs, reservations and storage. You query them with ordinary SQL, which makes them the way to answer questions like "which tables have a column called `email`", "which partitions changed today" or "who ran the most expensive queries this week".

## Dataset or region

Every view must be qualified. Most table-level views take a dataset, and return metadata for that dataset only:

**the tables of bigquery-public-data.samples**

```sql
SELECT table_name, table_type, DATE(creation_time) AS created
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.TABLES`
ORDER BY table_name
```

Result in BigQuery (7 rows):

| table_name | table_type | created |
| --- | --- | --- |
| github_nested | BASE TABLE | 2016-03-14 |
| github_timeline | BASE TABLE | 2016-03-14 |
| gsod | BASE TABLE | 2016-03-14 |
| natality | BASE TABLE | 2016-03-14 |
| shakespeare | BASE TABLE | 2016-03-14 |
| trigrams | BASE TABLE | 2016-03-14 |
| wikipedia | BASE TABLE | 2016-03-14 |

Views about the whole project, like `JOBS` and `SCHEMATA`, take a region qualifier instead: `` `region-us`.INFORMATION_SCHEMA.JOBS ``. A region qualifier covers one location only. `region-us` is the US multi-region and doesn't include `us-central1` or other single US regions; query each location separately.

The views that accept a dataset qualifier are `TABLES`, `TABLE_OPTIONS`, `COLUMNS`, `COLUMN_FIELD_PATHS`, `PARTITIONS`, `VIEWS`, `MATERIALIZED_VIEWS`, `ROUTINES`, `ROUTINE_OPTIONS` and `PARAMETERS`. Most of them also accept a region, which then returns that view across every dataset of the project in the region. `PARTITIONS` and the search index views take a dataset only.

## The name is case-sensitive

`INFORMATION_SCHEMA` and the view name must be in upper case. In lower case, BigQuery reads `information_schema` as an ordinary dataset name and looks for a dataset of that name:

**information_schema.tables in lower case**

```sql
SELECT table_name
FROM `bigquery-public-data.samples.information_schema.tables`
```

BigQuery rejects this query:

```text
Not found: Dataset bigquery-public-data:samples.information_schema was not found in location US
```

## Columns and nested fields

`COLUMNS` has one row per top-level column:

**the columns of samples.shakespeare**

```sql
SELECT column_name, data_type, is_nullable
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'shakespeare'
ORDER BY ordinal_position
```

Result in BigQuery (4 rows):

| column_name | data_type | is_nullable |
| --- | --- | --- |
| word | STRING | NO |
| word_count | INT64 | NO |
| corpus | STRING | NO |
| corpus_date | INT64 | NO |

`is_nullable` is the string `'YES'` or `'NO'`. `data_type` is the full type, like `ARRAY<STRUCT<sku STRING, qty INT64>>`. For the fields inside structs, use `COLUMN_FIELD_PATHS`, which has one row per field path:

**fields of the repository struct in samples.github_nested**

```sql
SELECT field_path, data_type
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = 'github_nested' AND field_path LIKE 'repository.%'
ORDER BY field_path
LIMIT 6
```

Result in BigQuery (6 rows):

| field_path | data_type |
| --- | --- |
| repository.created_at | STRING |
| repository.description | STRING |
| repository.fork | BOOL |
| repository.forks | INT64 |
| repository.has_downloads | BOOL |
| repository.has_issues | BOOL |

To find every table in a dataset that has a column, search `COLUMN_FIELD_PATHS` by `field_path` or `COLUMNS` by `column_name`:

```sql
SELECT table_name, field_path, data_type
FROM mydataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE LOWER(field_path) LIKE '%email%';
```

## The views you will use most

| View                                                     | Scope             | Use it to                                                              |
| -------------------------------------------------------- | ----------------- | ---------------------------------------------------------------------- |
| `TABLES`, `TABLE_OPTIONS`                                | dataset or region | list tables, see their type, DDL (`ddl` column), expiration and labels |
| `COLUMNS`, `COLUMN_FIELD_PATHS`                          | dataset or region | find columns and their types                                           |
| `PARTITIONS`                                             | dataset           | see each partition's rows, bytes and last change                       |
| `TABLE_STORAGE`                                          | region            | logical, physical, active, long-term and time travel bytes per table   |
| `JOBS` (`JOBS_BY_PROJECT`), `JOBS_BY_USER`               | region            | job history: who ran what, bytes billed, slot time, errors             |
| `JOBS_TIMELINE`                                          | region            | slot use per second, for capacity planning                             |
| `VIEWS`, `ROUTINES`                                      | dataset or region | the SQL of views and functions                                         |
| `TABLE_CONSTRAINTS`, `KEY_COLUMN_USAGE`                  | dataset           | primary and foreign keys                                               |
| `SESSIONS_BY_USER`, `STREAMING_TIMELINE`, `RESERVATIONS` | region            | sessions, streaming volume, reservations                               |

The `ddl` column of `TABLES` holds the `CREATE TABLE` statement that recreates the table, which is the quickest way to copy a schema with its options. In Querylab.io, right-click a table, choose SQL Templates, then CREATE TABLE to get that statement built from the table's schema, partitioning and clustering (see [DDL operations](https://docs.querylab.io/schema-browser/ddl-operations/)). The table's Details and Schema tabs show the `TABLES` and `COLUMNS` metadata (see [Table information](https://docs.querylab.io/schema-browser/table-information/)):

```sql
SELECT ddl
FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'orders';
```

Querylab.io's [Storage statistics](https://docs.querylab.io/data-management/storage-stats/) show the logical, physical, long-term and time travel bytes that `TABLE_STORAGE` holds, for a project, dataset or table, priced under both billing models. For job history and cost analysis, see [Monitoring jobs and costs](https://docs.querylab.io/bigquery/monitoring-jobs-and-costs/).

## What it costs

With on-demand pricing, every `INFORMATION_SCHEMA` query is billed at least 10 MB, even when it reads less. A dry run already shows that number:

**the estimate for a small metadata query**

```sql
SELECT table_name
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.TABLES`
```

BigQuery dry run: this query processes 10.0 MB.

Results are never cached, so each run is billed again, even with the same text. That is a fraction of a cent per query, but a dashboard that polls `JOBS` every minute adds up. Large `JOBS` queries over long time ranges can read far more than 10 MB; filter on `creation_time`, the view's partitioning column.

The filter has to be on `creation_time`. `start_time` and `end_time` look like they would do the same, but they don't prune. We dry-ran three queries on one project's `` `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT ``:

| Filter                                                      | Bytes processed |
| ----------------------------------------------------------- | --------------- |
| none                                                        | 106,579,826     |
| `creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)` | 1,308,333       |
| `start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)`    | 106,579,826     |

The `start_time` filter returns the same rows as it would otherwise, but reads the whole history. The 1.3 MB case is still billed as 10 MB, as above, so on a small project the bill is the same and only the processed bytes show it. The gap is in the bytes a large project scans. The numbers are from our project and will differ on yours.

## Storage billing model and TABLE_STORAGE

A dataset's storage billing model is one of its options, and `SCHEMATA_OPTIONS` lists it:

```sql
SELECT schema_name, option_value
FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE option_name = 'storage_billing_model';
```

A dataset that uses physical billing has a row with `PHYSICAL`. A dataset that was never switched has no row at all, not a row with `LOGICAL` or NULL. On our project, one dataset had a `storage_billing_model` row, and every other dataset was missing from the result. Read "no row" as logical billing, the default. To list every dataset with its model, `LEFT JOIN` from `SCHEMATA` and use `COALESCE(option_value, 'LOGICAL')`.

`TABLE_STORAGE` has both logical and physical byte columns for every table, whichever model the dataset uses. You are billed on one set, chosen by the dataset's model. Adding `total_logical_bytes` and `total_physical_bytes` counts the same data twice. Google's forecast formulas use the logical columns for a logical dataset and the physical ones for a physical dataset. See [Storage pricing](https://docs.querylab.io/bigquery/storage-pricing/) for a query that prices both.

## Permissions

You see metadata only for objects you have access to: a dataset qualifier needs permissions on the dataset, a region qualifier on the project. `JOBS_BY_USER` shows your own jobs; `JOBS` for the whole project needs `bigquery.jobs.listAll`, which comes with the BigQuery Resource Viewer role. `TABLE_STORAGE` needs `bigquery.tables.get` and `bigquery.tables.list`, which the BigQuery Metadata Viewer role has.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to INFORMATION_SCHEMA](https://cloud.google.com/bigquery/docs/information-schema-intro)

- [COLUMN_FIELD_PATHS view](https://cloud.google.com/bigquery/docs/information-schema-column-field-paths)

- [TABLE_STORAGE view](https://cloud.google.com/bigquery/docs/information-schema-table-storage)