11 May 20 · npack · #Bigquery ·   Bookmark   ×

Find all Table columns, datatype and metadata information in Google BigQuery using Information Schema

We often want to find column names of a table (table metadata) in Bigquery, like the list of all the columns and their datatypes and constraints etc. Is there anything similar to dbc.tables or dbo.alltables in Bigquery ?

Yes, Bigquery offers this information via Information Schema:

Finding table level metadata within a dataset:

SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_type="BASE TABLE"
+----------------+---------------+----------------+------------+--------------------+---------------------+
| table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time |
+----------------+---------------+----------------+------------+--------------------+---------------------+
| myproject | mydataset | mytable1 | BASE TABLE | NO | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+

Finding Column level metadata across Tables in a dataset:

SELECT * FROM
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="population_by_zip_2010"
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| table_name | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode | 1 | NO | STRING | NO | NO | NO | NULL |
| population_by_zip_2010 | geo_id | 2 | YES | STRING | NO | NO | NO | NULL |
| population_by_zip_2010 | minimum_age | 3 | YES | INT64 | NO | NO | NO | NULL |
| population_by_zip_2010 | maximum_age | 4 | YES | INT64 | NO | NO | NO | NULL |
| population_by_zip_2010 | gender | 5 | YES | STRING | NO | NO | NO | NULL |
| population_by_zip_2010 | population | 6 | YES | INT64 | NO | NO | NO | NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+

npack

posted on 11 May 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds