CODEWITHSUNDEEP
sqlgoogle-bigquery
emiliag345
emiliag345

Reputation: 39

Listing All Tables and Fields Google BigQuery

Is there a SQL query I can use in the Web UI in Google BigQuery that would return a list of all of the tables and fields/schema within a database? So far I've only been able to find a "TABLES_SUMMARY" command but that does not give me any info on the fields/schema. I do not want to have to click on each individual table to see what's in it.

Upvotes: 3

Views: 8144

Answers (3)

Elliott Brossard
Elliott Brossard

Reputation: 33705

BigQuery provides support for INFORMATION_SCHEMA views, which are in beta at the time of this writing. If you want to get the schema of multiple tables, you can query the COLUMNS view, e.g.:

SELECT table_name, column_name, data_type
FROM `bigquery-public-data`.stackoverflow.INFORMATION_SCHEMA.COLUMNS
ORDER BY table_name, ordinal_position

This returns:

Row table_name  column_name         data_type   
1   badges      id                  INT64
2   badges      name                STRING
3   badges      date                TIMESTAMP
4   badges      user_id             INT64
5   badges      class               INT64
6   badges      tag_based           BOOL
7   comments    id                  INT64
8   comments    text                STRING
9   comments    creation_date       TIMESTAMP
10  comments    post_id             INT64
11  comments    user_id             INT64
12  comments    user_display_name   STRING
13  comments    score               INT64
...

Upvotes: 4

FKrauss
FKrauss

Reputation: 418

someone posted a workaround here.

Depending on how many columns you have, you can do something similar without much hassle

Upvotes: 0

Shereen
Shereen

Reputation: 139

You can try below query

SELECT * FROM [DB Name].INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME

Upvotes: 0

Related Questions