Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

CommandDescription
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.