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
SELECTinstead ofSHOW DATABASES;. TheSHOWcommand 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
| Command | Description |
|---|---|
| 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.
