Search for a column in a bigQuery project or dataset-CodePudding

I am looking for a table in my bigQuery project to give me information about parts size. I am not sure which dataset and table I have to search for.

In the project, there are tens of datasets with tens of tables in each. How I can find "partSize" in which table?

Is there any query to search schemas of all the tables in a dataset or project?

CodePudding user response：

Data Catalog has a search function

CodePudding user response：

select 
  table_catalog,
  table_schema, 
  table_name,   
  column_name 
from  dataset_name.INFORMATION_SCHEMA.COLUMNS 
where lower(column_name) like '%partsize%'

Can give you for a specific dataset, the name of the tables where you can find the column you are looking for.

If there a lot of dataset:

select 

concat("select table_catalog, table_schema, table_name, column_name from  ", schema_name, ".INFORMATION_SCHEMA.COLUMNS where lower(column_name) like '%%';")

from `region-us`.INFORMATION_SCHEMA.SCHEMATA

Can generate a query for all the datasets that are in that region. Pasting the results in the clipboard and back in the console should work. It is a bit ugly though, maybe there is a better way.