In google-bigquery, how do I get list of tables order by size?

Question

I need to list down all the BQ tables in a dataset order by size, to find out tables occupying more space. How to achieve this?

Thanks!

Answer 1

#standardSQL
SELECT table_id,
    DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
    DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date,
    row_count,
    size_bytes,
    CASE
        WHEN type = 1 THEN 'table'
        WHEN type = 2 THEN 'view'
        WHEN type = 3 THEN 'external'
        ELSE '?'
    END AS type,
    TIMESTAMP_MILLIS(creation_time) AS creation_time,
    TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
    dataset_id,
    project_id
FROM `yourProject.yourDataset.__TABLES__`
ORDER BY size_bytes DESC  

Above query gives you more than just size - enjoy :o)