access_time 5 months ago languageEnglish

Get List of Tables/Views and Schema in BigQuery

visibility 206 comment 0

BigQuery provides system views to get a list of tables and views in a certain dataset. 


The following query retrieves all the tables and views in dataset named `test`:

SELECT * FROM `test`.__TABLES__;
The results looks similar to something below:
The information returned includes:
  • Project ID
  • Dataset ID
  • Object creation time
  • Last modification time
  • Record count (for tables)
  • Size in bytes (for tables)
  • Type: 1 - table; 2- View

Retrieve object schema

Sometimes it is useful to retrieve the schema information of an object in BigQuery.

There are system views can be used to retrieve metadata information.

Retrieve table and view list


The results looks similar to the following screenshot:

Retrieve table schema

The following query returns the schema metadata of table dim_customer in dataset test.

WHERE table_name='dim_customer' AND table_schema='test';

The output shows all the columns in table test.dim_customer:


Getting table metadata using INFORMATION_SCHEMA  |  BigQuery (

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

Pandas - Save DataFrame to BigQuery
visibility 168
thumb_up 0
access_time 5 months ago
PySpark: Read File in Google Cloud Storage
visibility 1050
thumb_up 0
access_time 4 months ago