Organiza tus páginas con colecciones
Guarda y categoriza el contenido según tus preferencias.
Vista de SCHEMATA
La vista INFORMATION_SCHEMA.SCHEMATA proporciona información sobre los conjuntos de datos de un proyecto o una región. La vista muestra una fila para cada conjunto de datos.
Antes de comenzar
Para consultar la vista SCHEMATA en busca de metadatos del conjunto de datos, necesitas el permiso bigquery.datasets.get de Identity and Access Management (IAM) a nivel de proyecto.
Cada una de las siguientes funciones predefinidas de IAM incluye los permisos que necesitas para obtener la vista SCHEMATA:
Las consultas realizadas a esta vista deben incluir un calificador de región. Si no especificas un calificador regional, los metadatos se recuperan de la región de EE.UU.
En la siguiente tabla, se explica el permiso de la región para esta vista:
[[["Fácil de comprender","easyToUnderstand","thumb-up"],["Resolvió mi problema","solvedMyProblem","thumb-up"],["Otro","otherUp","thumb-up"]],[["Difícil de entender","hardToUnderstand","thumb-down"],["Información o código de muestra incorrectos","incorrectInformationOrSampleCode","thumb-down"],["Faltan la información o los ejemplos que necesito","missingTheInformationSamplesINeed","thumb-down"],["Problema de traducción","translationIssue","thumb-down"],["Otro","otherDown","thumb-down"]],["Última actualización: 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```"]]
