SCHEMATA_LINKS view

The INFORMATION_SCHEMA.SCHEMATA_LINKS view contains one row for each linked dataset. A linked dataset links to a shared dataset in a project to which the current user has access.

Required permission

To query the INFORMATION_SCHEMA.SCHEMATA_LINKS view, you need the bigquery.datasets.get Identity and Access Management (IAM) permission at the project level.

Each of the following predefined IAM roles includes the permissions that you need in order to query the INFORMATION_SCHEMA.SCHEMATA_LINKS view:

roles/bigquery.admin
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.dataViewer

For more information about BigQuery permissions, see Access control with IAM.

Schema

The INFORMATION_SCHEMA.SCHEMATA_LINKS view has the following schema:

Column name	Data type	Value
`CATALOG_NAME`	`STRING`	The name of the project that contains the source dataset.
`SCHEMA_NAME`	`STRING`	The name of the source dataset. The dataset name is also referred to as the `datasetId`.
`LINKED_SCHEMA_CATALOG_NUMBER`	`STRING`	The project number of the project that contains the linked dataset.
`LINKED_SCHEMA_CATALOG_NAME`	`STRING`	The project name of the project that contains the linked dataset.
`LINKED_SCHEMA_NAME`	`STRING`	The name of the linked dataset. The dataset name is also referred to as the `datasetId`.
`LINKED_SCHEMA_CREATION_TIME`	`TIMESTAMP`	The time when the linked dataset was created.
`LINKED_SCHEMA_ORG_DISPLAY_NAME`	`STRING`	The display name of the organization in which the linked dataset is created.

Scope and syntax

Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from the US region. The following table explains the region scope for this view:

View Name	Resource scope	Region scope
`[PROJECT_ID.]INFORMATION_SCHEMA.SCHEMATA_LINKS`	Project level	US region
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA_LINKS	Project level	`REGION`

Replace the following:

Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

REGION: any dataset region name. For example, `region-us`.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

Examples

This section lists examples to query the INFORMATION_SCHEMA.SCHEMATA_LINKS view.

Example: List all linked datasets against another project

The following example lists all the linked datasets against another project named otherproject within the eu region:

SELECT * FROM `otherproject`.`region-eu`.INFORMATION_SCHEMA.SCHEMATA_LINKS;

The output is similar to the following. Some columns are omitted to simplify the output.

+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+
|  catalog_name  | schema_name | linked_schema_catalog_name | linked_schema_catalog_number | linked_schema_name | linked_schema_org_display_name |
+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+
| otherproject   | myschema1   | subscriptionproject1       |                 974999999291 | subscriptionld1    | subscriptionorg2               |
| otherproject   | myschema2   | subscriptionproject2       |                 974999999292 | subscriptionld2    | subscriptionorg2               |
| otherproject   | myschema3   | subscriptionproject3       |                 974999999293 | subscriptionld3    | subscriptionorg3               |
+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+

Example: List all linked datasets by a shared dataset

The following example lists all the all the linked datasets by a shared dataset named sharedataset in the US multi-region:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA_LINKS WHERE schema_name = 'sharedataset';

The output is similar to the following. Some columns are omitted to simplify the output.

+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+
|  catalog_name  | schema_name | linked_schema_catalog_name | linked_schema_catalog_number | linked_schema_name | linked_schema_org_display_name |
+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+
| myproject      | sharedataset| subscriptionproject1       |                 674999999291 | subscriptionld1    | subscriptionorg1               |
| myproject      | sharedataset| subscriptionproject2       |                 774999999292 | subscriptionld2    | subscriptionorg3               |
+----------------+-------------+----------------------------+------------------------------+--------------------+--------------------------------+