ADA-C01 Question 399
Single answerViews available from the INFORMATION_SCHEMAA Snowflake administrator needs to produce a report for one application database showing every table and view, along with each object's owner role and creation timestamp. The administrator wants to use only views from that database's INFORMATION_SCHEMA and avoid account-wide usage views. Which INFORMATION_SCHEMA view should be queried to meet this requirement most directly?
- A
TABLES
- B
OBJECT_PRIVILEGES
- C
TABLE_PRIVILEGES
- D
SCHEMATA
Show answer and explanation
Correct answer: A
Explanation
Within each Snowflake database, INFORMATION_SCHEMA exposes ANSI-style metadata views for objects in that database. For an inventory of tables and views, INFORMATION_SCHEMA.TABLES is the correct and most direct choice because Snowflake includes both base tables and views in this view, differentiated by TABLE_TYPE. It also exposes useful administrative metadata such as OWNER and CREATED. By contrast, OBJECT_PRIVILEGES and TABLE_PRIVILEGES focus on grants, and SCHEMATA only describes schemas. This aligns with Snowflake documentation for INFORMATION_SCHEMA views, where TABLES is used to enumerate relational objects in a database without relying on account-level SNOWFLAKE.ACCOUNT_USAGE views.
- A. Correct.
Correct. INFORMATION_SCHEMA.TABLES returns metadata for tables and views in the current database, including columns such as TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATED, and OWNER. This makes it the most direct INFORMATION_SCHEMA source when the requirement is to list both tables and views together with ownership and creation time for a single database.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.OBJECT_PRIVILEGES shows grants on objects, not the authoritative inventory of all tables and views. While it can show which privileges were granted and to whom, it does not serve as the primary object catalog for listing all tables and views with creation timestamps. Choosing this option reflects a common misconception that privilege metadata is the same as object metadata.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLE_PRIVILEGES is limited to privilege grants on tables and similar objects. It does not provide a complete list of both tables and views with owner and creation timestamp. It is useful for auditing access, not for building a database object inventory report.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA.SCHEMATA contains schema-level metadata, such as schema names and owners, but not row-level entries for every table or view. It is too high-level for a requirement that needs object-by-object details.