CODEWITHSUNDEEP
google-bigquery
neydroydrec
neydroydrec

Reputation: 7313

How to query BigQuery view and table dependencies?

After building a number of SQL views as reports on BigQuery, I find myself in need of identifying views and tables dependencies.

How can I query: "List all views that depend on view/table T?"

Upvotes: 4

Views: 4309

Answers (2)

sbaker
sbaker

Reputation: 13

Found Query Stash's answer helpful, however a slight addition that helped me: if you don't want to be searching each dataset seperately, you can search everything within the project by replacing dataset_name with the region.

SELECT *
FROM region-region_name.INFORMATION_SCHEMA.VIEWS
WHERE REGEXP_CONTAINS(view_definition, 'dataset_name.table_name')

Upvotes: 1

Query Stash
Query Stash

Reputation: 159

You can run the following query that will list all views, this can be done on an individual dataset basis. Of course if you have a set of datasets you could create one UNION ALL query for each dataset to combine the results.

SELECT *
FROM dataset_name.INFORMATION_SCHEMA.VIEWS
WHERE REGEXP_CONTAINS(view_definition, 'dataset_name.table_name')

Replacing dataset_name and table_name in the above query.

view_definition in the schema table contains the actual SQL code to create the view. So we are essentially just searching that field for the table name, same would work if you are looking for a view being used in a view.

Upvotes: 5

Related Questions