How can I get the list of all columns in a BigQuery table and dataset

Question

I would like to query all columns and their description in a table/dataset. I'm looking similar metadata tables like __TABLES_SUMMARY__ and __TABLES__.

The goal is to build a data dictionary report in Data Studio for the BigQuery tables.

Answer 1

You can now query the list of columns that way:

SELECT column_name, data_type
FROM `myproject`.mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'mytable'

You need to replace myproject, mydataset, mytable by your values.

Answer 2

You can use bq show in CLI

For example,

Issue the bq show command to display all table information. Use the --schema flag to display only table schema information. The --format flag can be used to control the output.

If you are getting information about a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Where:

[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset.
[TABLE] is the name of the table.

Similarly for dataset :

bq show --format=prettyjson [PROJECT_ID]:[DATASET]