BigQuery provides system views to get a list of tables and views in a certain dataset.
\_\_TABLES\_\_
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
SELECT * FROM test.INFORMATION_SCHEMA.TABLES
The results looks similar to the following screenshot:
Retrieve table schema
The following query returns the schema metadata of table dim_customerin dataset test.
SELECT * EXCEPT(table_catalog) FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE table_name='dim_customer' AND table_schema='test';
The output shows all the columns in table test.dim_customer:
References
Getting table metadata using INFORMATION_SCHEMA | BigQuery (google.com)