Obtain the size, number of rows and last modified time of all tables within our dataset

PHOTO EMBED

Mon Dec 20 2021 00:44:36 GMT+0000 (UTC)

Saved by @admariner

#standardSQL 
SELECT dataset_id, table_id, 
# Convert size in bytes to GB 
ROUND(size_bytes/POW(10,9),2) AS size_gb, 
# Convert creation_time and last_modified_time from UNIX EPOCH format to a timestamp 
TIMESTAMP_MILLIS(creation_time) AS creation_time, TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
row_count, 
# Convert table type from numerical value to description 
CASE 
WHEN type = 1 THEN 'table' 
WHEN type = 2 THEN 'view' 
ELSE NULL 
END AS type 
FROM `bigquery-public-data.ethereum_blockchain`.__TABLES__ 
ORDER BY size_gb DESC;
content_copyCOPY

https://towardsdatascience.com/a-simple-way-to-query-table-metadata-in-google-bigquery-92dc7f1edec1