Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to List Databases in Snowflake: Syntax and Examples

To list databases in Snowflake, use the SHOW DATABASES; command. This command displays all databases accessible to your current user in a table format.
📐

Syntax

The command to list databases in Snowflake is simple and straightforward.

  • SHOW DATABASES;: Lists all databases you can access.

This command does not require any parameters and returns a list of database names along with details like creation date and owner.

sql
SHOW DATABASES;
💻

Example

This example shows how to run the command and what output to expect. It lists all databases available to your user.

sql
SHOW DATABASES;
Output
name | created_on | owner -----------|---------------------|-------- TEST_DB | 2023-05-01 10:00:00 | ADMIN SALES_DB | 2023-06-15 14:30:00 | ANALYST MARKETING_DB | 2023-07-20 09:45:00 | MARKETING
⚠️

Common Pitfalls

Some common mistakes when listing databases in Snowflake include:

  • Trying to use SELECT instead of SHOW DATABASES;. The SHOW command is required.
  • Not having the right permissions to see databases, which results in an empty list or error.
  • Expecting the command to filter databases; it lists all accessible databases without filters.

Correct usage example:

sql
/* Wrong: This will cause an error */
SELECT * FROM databases;

/* Right: Use SHOW command */
SHOW DATABASES;
📊

Quick Reference

CommandDescription
SHOW DATABASES;Lists all databases accessible to the current user
SHOW SCHEMAS;Lists schemas inside a database (related command)
SHOW TABLES;Lists tables inside a schema (related command)
✅

Key Takeaways

Use the command SHOW DATABASES; to list all accessible databases in Snowflake.
You must have the right permissions to see databases; otherwise, the list may be empty.
SHOW commands are used for listing objects in Snowflake, not SELECT queries.
The output includes database names, creation dates, and owners.
Related commands like SHOW SCHEMAS; and SHOW TABLES; help explore deeper levels.