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?"
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.