Mantieni tutto organizzato con le raccolte
Salva e classifica i contenuti in base alle tue preferenze.
Visualizzazione SCHEMATA
La visualizzazione INFORMATION_SCHEMA.SCHEMATA fornisce informazioni sui set di dati
in un progetto o una regione. La visualizzazione restituisce una riga per ogni set di dati.
Prima di iniziare
Per eseguire query sulla visualizzazione SCHEMATA per i metadati del set di dati, devi disporre dell'autorizzazione bigquery.datasets.get IAM (Identity and Access Management) a livello di progetto.
Ciascuno dei seguenti ruoli IAM predefiniti include le autorizzazioni necessarie per visualizzare la visualizzazione SCHEMATA:
Quando esegui una query nella visualizzazione INFORMATION_SCHEMA.SCHEMATA, i risultati della query contengono una riga per ogni set di dati nel progetto specificato.
La vista INFORMATION_SCHEMA.SCHEMATA ha lo schema seguente:
Nome colonna
Tipo di dati
Valore
CATALOG_NAME
STRING
Il nome del progetto contenente il set di dati
SCHEMA_NAME
STRING
Il nome del set di dati, noto anche come datasetId
SCHEMA_OWNER
STRING
Il valore è sempre NULL
CREATION_TIME
TIMESTAMP
La data e l'ora di creazione del set di dati
LAST_MODIFIED_TIME
TIMESTAMP
L'ora dell'ultima modifica del set di dati
LOCATION
STRING
La posizione geografica del set di dati
DDL
STRING
L'istruzione CREATE SCHEMA
DDL che può essere utilizzata per creare il set di dati
Le query su questa vista devono includere un qualificatore
della regione. Se non specifichi un qualificatore regionale, i metadati vengono recuperati dalla regione degli Stati Uniti.
La tabella seguente illustra l'ambito della regione per questa visualizzazione:
[[["Facile da capire","easyToUnderstand","thumb-up"],["Il problema è stato risolto","solvedMyProblem","thumb-up"],["Altra","otherUp","thumb-up"]],[["Difficile da capire","hardToUnderstand","thumb-down"],["Informazioni o codice di esempio errati","incorrectInformationOrSampleCode","thumb-down"],["Mancano le informazioni o gli esempi di cui ho bisogno","missingTheInformationSamplesINeed","thumb-down"],["Problema di traduzione","translationIssue","thumb-down"],["Altra","otherDown","thumb-down"]],["Ultimo aggiornamento 2025-09-04 UTC."],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.SCHEMATA\u003c/code\u003e view provides metadata about datasets within a specific project or region, with each row representing a single dataset.\u003c/p\u003e\n"],["\u003cp\u003eTo query the \u003ccode\u003eSCHEMATA\u003c/code\u003e view, users need the \u003ccode\u003ebigquery.datasets.get\u003c/code\u003e IAM permission, which is included in roles like \u003ccode\u003eroles/bigquery.admin\u003c/code\u003e, \u003ccode\u003eroles/bigquery.dataEditor\u003c/code\u003e, \u003ccode\u003eroles/bigquery.dataOwner\u003c/code\u003e, and \u003ccode\u003eroles/bigquery.dataViewer\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eQueries against the \u003ccode\u003eSCHEMATA\u003c/code\u003e view must include a region qualifier, and the query execution location must match the region of the view; if no region is specified, it defaults to the US region.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eSCHEMATA\u003c/code\u003e view's schema includes columns such as \u003ccode\u003eCATALOG_NAME\u003c/code\u003e, \u003ccode\u003eSCHEMA_NAME\u003c/code\u003e, \u003ccode\u003eCREATION_TIME\u003c/code\u003e, \u003ccode\u003eLAST_MODIFIED_TIME\u003c/code\u003e, \u003ccode\u003eLOCATION\u003c/code\u003e, and \u003ccode\u003eDDL\u003c/code\u003e, providing details about each dataset.\u003c/p\u003e\n"],["\u003cp\u003eThe result for the view returns one row for each dataset in the specified project, and an example is provided with some columns omitted for better readability.\u003c/p\u003e\n"]]],[],null,["# SCHEMATA view\n=============\n\nThe `INFORMATION_SCHEMA.SCHEMATA` view provides information about the datasets\nin a project or region. The view returns one row for each dataset.\n\nBefore you begin\n----------------\n\nTo query the `SCHEMATA` view for dataset metadata, you need the `bigquery.datasets.get`\nIdentity and Access Management (IAM) permission at the project level.\n\nEach of the following predefined IAM roles includes the\npermissions that you need in order to get the `SCHEMATA`\nview:\n\n- `roles/bigquery.admin`\n- `roles/bigquery.dataEditor`\n- `roles/bigquery.dataOwner`\n- `roles/bigquery.dataViewer`\n\nFor more information about BigQuery permissions, see\n[Access control with IAM](/bigquery/docs/access-control).\n\nSchema\n------\n\nWhen you query the `INFORMATION_SCHEMA.SCHEMATA` view, the query results contain one row for each dataset in the specified project.\n\n\u003cbr /\u003e\n\nThe `INFORMATION_SCHEMA.SCHEMATA` view has the following schema:\n\nScope and syntax\n----------------\n\nQueries against this view must include a [region\nqualifier](/bigquery/docs/information-schema-intro#syntax). If you do not\nspecify a regional qualifier, metadata is retrieved from the US region.\nThe following table explains the region scope for this view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: any [dataset region name](/bigquery/docs/locations). For example, ```region-us```.\n\n \u003cbr /\u003e\n\n \u003cbr /\u003e\n\n | **Note:** You must use [a region qualifier](/bigquery/docs/information-schema-intro#region_qualifier) to query `INFORMATION_SCHEMA` views. The location of the query execution must match the region of the `INFORMATION_SCHEMA` view.\n\n\u003cbr /\u003e\n\n**Example** \n\n -- Returns metadata for datasets in a region.\n SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;\n\nExample\n-------\n\nTo run the query against a project other than your default project, add the\nproject ID to the dataset in the following format: \n\n```bash\n`PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA\n```\nfor example, ```myproject`.INFORMATION_SCHEMA.SCHEMATA``.\n\n\u003cbr /\u003e\n\n```googlesql\nSELECT\n * EXCEPT (schema_owner)\nFROM\n INFORMATION_SCHEMA.SCHEMATA;\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following. For readability, some columns\nare excluded from the result. \n\n```\n+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+\n| catalog_name | schema_name | creation_time | last_modified_time | location | ddl |\n+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+\n| myproject | mydataset1 | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US | CREATE SCHEMA `myproject.mydataset1` |\n| | | | | | OPTIONS( |\n| | | | | | location=\"us\" |\n| | | | | | ); |\n+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+\n| myproject | mydataset2 | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US | CREATE SCHEMA `myproject.mydataset2` |\n| | | | | | OPTIONS( |\n| | | | | | default_partition_expiration_days=3.0, |\n| | | | | | location=\"us\" |\n| | | | | | ); |\n+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+\n| myproject | mydataset3 | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | US | CREATE SCHEMA `myproject.mydataset3` |\n| | | | | | OPTIONS( |\n| | | | | | description=\"My dataset\", |\n| | | | | | location=\"us\" |\n| | | | | | ); |\n+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+\n```"]]
