BQ Get labels from information schema

Question

I need to get the labels of all the BQ tables in a project. Currently the only way I found is to loop over all the tables and retrieve the labels.

        tables = client.list_tables(dataset_id)
        for table in tables:
            if table.labels:
                for label, value in table.labels.items():

This approach works but is time consuming. Is there any possibility to get the labels using a unique BQ query? INFORMATION_SCHEMA.TABLES doesn't return the labels.

Answer 1

You can define an option to return the labels from the INFORMATION SCHEMA.

SELECT
  *
FROM
  INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE
  schema_name = 'schema'
  AND option_name = 'labels';