INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION view contain one row for
each table or materialized view for the whole organization associated with the
current project. The data in this table is not kept in real time, and might be
delayed by a few seconds to a few minutes.
The table storage views give you a convenient way to observe your current storage consumption, and in addition provide details on whether your storage uses logical uncompressed bytes, physical compressed bytes, or time travel bytes. This information can help you with tasks like planning for future growth and understanding the update patterns for tables.
Data included in the
*_BYTES columns in the table storage views include information about your
use of storage bytes. This information is determined by looking at your storage
usage for materialized views and the following types of tables:
- Permanent tables created through any of the methods described in Create and use tables.
- Temporary tables generated in sessions and multi-statement queries ("scripts").
Data stored in the
query results cache
is not billed to you and so is not included in the
*_BYTES column values.
Storage usage by clones and snapshots is billed, but information about clones and snapshots isn't included in the table storage views.
Forecast storage billing
In order to forecast the monthly storage billing for a dataset, you can use
physical *_BYTES columns in this view, depending
dataset storage billing model
used by the dataset. Please note that this is only a rough forecast, and
the precise billing amounts are calculated based on the usage by
BigQuery storage billing infrastructure and visible in
For datasets that use a logical billing model, you can forecast your monthly storage costs as follows:
ACTIVE_LOGICAL_BYTES value /
POW(1024, 3)) * active logical bytes pricing) +
LONG_TERM_LOGICAL_BYTES value /
POW(1024, 3)) * long-term logical bytes pricing)
ACTIVE_LOGICAL_BYTES value for a table reflects the active bytes
currently used by that table.
For datasets that use a physical billing model, you can forecast your storage costs as follows:
ACTIVE_PHYSICAL_BYTES value /
POW(1024, 3)) * active physical bytes pricing) +
LONG_TERM_PHYSICAL_BYTES value /
POW(1024, 3)) * long-term physical bytes pricing)
ACTIVE_PHYSICAL_BYTES value for a table reflects the active bytes
currently used by that table plus the bytes used for time travel for that table.
To see the active bytes of the table alone, subtract the
TIME_TRAVEL_PHYSICAL_BYTES value from the
For more information, see Storage pricing.
To query the
INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION view, you need the following
Identity and Access Management (IAM) permissions for your organization:
Each of the following predefined IAM roles includes the preceding permissions:
This schema view is only available to users with defined Google Cloud organizations.
For more information about BigQuery permissions, see Access control with IAM.
INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION view has the following schema:
|Column name||Data type||Value|
||The project ID of the project that contains the dataset|
||The project number of the project that contains the dataset|
||The name of the dataset that contains the table or materialized view,
also referred to as the
||The name of the table or materialized view, also referred to as the
||The table's creation time|
||Indicates whether or not the table is deleted|
||The most recent time that data was written to the table.|
||The total number of rows in the table or materialized view|
||The number of partitions present in the table or materialized view. Unpartitioned tables return 0.|
||Total number of logical (uncompressed) bytes in the table or materialized view|
||Number of logical (uncompressed) bytes that are less than 90 days old|
||Number of logical (uncompressed) bytes that are more than 90 days old|
||Total number of physical (compressed) bytes used for storage, including active, long term, and time travel (deleted or changed data) bytes|
||Number of physical (compressed) bytes less than 90 days old, including time travel (deleted or changed data) bytes|
||Number of physical (compressed) bytes more than 90 days old|
||Number of physical (compressed) bytes used by time travel storage (deleted or changed data)|
||The type of table. For example, `EXTERNAL` or `BASE TABLE`|
Scope and syntax
Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region scope for this view:
|View name||Resource scope||Region scope|
||Organization that contains the specified project||
PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
REGION: any dataset region name. For example,
SELECT CONCAT(v1.table_catalog, ":", v1.table_schema, ".", v1.table_name) AS unmodified_table_name, FROM `region-REGION`.INFORMATION_SCHEMA.TABLES v1 LEFT JOIN `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION v2 ON v1.table_name = v2.table_name WHERE v2.table_name IS NULL;
The following example shows how to return storage information for tables in a specified project in an organization:
SELECT * FROM `myProject`.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION;
The following example shows how to return storage information by project for tables in an organization:
SELECT * FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION;
The following example shows you which projects in an organization are currently using the most storage.
SELECT project_id, SUM(total_logical_bytes) AS total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION GROUP BY project_id ORDER BY total_logical_bytes DESC;
The result is similar to the following:
+---------------------+---------------------+ | project_id | total_logical_bytes | +---------------------+---------------------+ | projecta | 971329178274633 | +---------------------+---------------------+ | projectb | 834638211024843 | +---------------------+---------------------+ | projectc | 562910385625126 | +---------------------+---------------------+