SOL-C01 practice question 4 of 30

Which command can be used to query the Information Schema in Snowflake to retrieve a list of tables?

Full set: 214 questions
Answer options
A: SELECT * FROM PUBLIC.INFORMATION_SCHEMA;
B: SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
C: SELECT * FROM INFORMATION_SCHEMA.TABLES; Correct answer
D: SELECT * FROM PUBLIC.TABLES;
Correct Answer:
C. SELECT * FROM INFORMATION_SCHEMA.TABLES;
Explanation

The Snowflake Information Schema is a read-only schema available in every database that provides metadata about the objects within it. To retrieve a list of tables and views accessible by the current user's role, the correct command is to query the TABLES view within the INFORMATIONSCHEMA. This view contains columns like TABLENAME, TABLESCHEMA, and TABLETYPE.

Why Incorrect

A. SELECT FROM PUBLIC.INFORMATIONSCHEMA;: This syntax is invalid. INFORMATIONSCHEMA is a schema containing views and cannot be queried directly as if it were a table.
B. SELECT FROM INFORMATIONSCHEMA.COLUMNS;: This command queries the COLUMNS view, which returns metadata about individual columns within tables, not a list of the tables themselves.
D. SELECT FROM PUBLIC.TABLES;: This would query a user-created table named TABLES in the PUBLIC schema, not the system-defined metadata view for listing all tables.

References

- Snowflake Documentation, "Information Schema". This document introduces the Information Schema as the source for object and account metadata.

- Snowflake Documentation, "TABLES View". This page explicitly describes the INFORMATIONSCHEMA.TABLES view and its purpose: "This Information Schema view displays a row for each table or view in the specified (or current) database."

Scroll to Top