How to List Schemas in Snowflake: Syntax and Examples
To list schemas in Snowflake, use the
SHOW SCHEMAS command to see all schemas in the current or specified database. You can also query the INFORMATION_SCHEMA.SCHEMATA view for detailed schema information.Syntax
The basic command to list schemas is SHOW SCHEMAS. You can optionally specify a database name to list schemas within that database.
SHOW SCHEMAS;— Lists all schemas in the current database.SHOW SCHEMAS IN DATABASE <database_name>;— Lists schemas in the specified database.SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;— Queries detailed schema info from the metadata view.
sql
SHOW SCHEMAS; SHOW SCHEMAS IN DATABASE my_database; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
Example
This example shows how to list all schemas in the current database and then how to list schemas in a specific database named SALES_DB. It also demonstrates querying the INFORMATION_SCHEMA.SCHEMATA view for schema details.
sql
USE DATABASE SALES_DB; SHOW SCHEMAS; SHOW SCHEMAS IN DATABASE SALES_DB; SELECT SCHEMA_NAME, CREATED, COMMENT FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = 'SALES_DB';
Output
name: SCHEMAS in current database
+------------------+
| name |
+------------------+
| PUBLIC |
| MARKETING |
| FINANCE |
+------------------+
name: SCHEMAS in SALES_DB
+------------------+
| name |
+------------------+
| PUBLIC |
| SALES |
| ANALYTICS |
+------------------+
SCHEMA_NAME | CREATED | COMMENT
------------+---------------------+---------
PUBLIC | 2023-01-15 10:00:00 |
SALES | 2023-02-20 09:30:00 | Sales data
ANALYTICS | 2023-03-05 14:45:00 | Analytics schemas
Common Pitfalls
One common mistake is running SHOW SCHEMAS without setting the correct database context, which lists schemas only for the current database. Another is confusing schemas with databases; schemas are inside databases. Also, querying INFORMATION_SCHEMA.SCHEMATA requires specifying the correct CATALOG_NAME (database name) to filter results properly.
sql
/* Wrong: No database selected, so shows schemas of default or current database */ SHOW SCHEMAS; /* Right: Specify database explicitly */ SHOW SCHEMAS IN DATABASE my_database; /* Wrong: Querying all schemas without filtering by database */ SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; /* Right: Filter by database name */ SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = 'my_database';
Quick Reference
| Command | Description |
|---|---|
| SHOW SCHEMAS; | Lists schemas in the current database |
| SHOW SCHEMAS IN DATABASE | Lists schemas in the specified database |
| SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; | Shows all schemas metadata (use filter for specific database) |
| SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = ' | Shows schemas metadata for one database |
Key Takeaways
Use SHOW SCHEMAS to list schemas in the current or specified database.
Set the correct database context to see the schemas you want.
INFORMATION_SCHEMA.SCHEMATA provides detailed schema metadata.
Always filter INFORMATION_SCHEMA queries by database name for accuracy.
Schemas exist inside databases, not independently.
