How to List Databases in Snowflake

How do I list databases in Snowflake?

SHOW DATABASES returns metadata for every database visible to the current role, with optional filters such as LIKE, STARTS WITH, and row limits.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does SHOW DATABASES do?

SHOW DATABASES displays metadata for every database your current role can see. The result set includes created_on, name, owner, comment, and retention_time. It never affects data, so it's safe for quick checks or automation scripts.

How do I list all databases in Snowflake?

Run SHOW DATABASES; while using any warehouse. The command returns every database—like ECOMMERCE, ANALYTICS, and FINANCE—that your role can access. Sort or filter the result in the UI, or pipe it to a client-side script for automation.

How can I filter databases by name pattern?

Add the LIKE or STARTS WITH clause. For instance, SHOW DATABASES LIKE 'ECOM%'; returns only databases whose names begin with ECOM. This is handy in multi-tenant setups.

How do I see just my own databases?

Combine SHOW DATABASES with RESULT_SCAN on the last query: SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE owner='MY_ROLE'; This keeps the inventory focused on what you administer.

Best practices when listing databases

Use TERSE to reduce columns when building dashboards. Always apply filters in large accounts to avoid hitting the 10,000-row cap. Incorporate result-set queries instead of exporting CSVs to automate audits.

Common mistakes and fixes

Missing warehouse: You still need an active warehouse session even though the command is metadata-only. Resume or switch to a tiny warehouse to avoid costs.

Relying on stale UI: Refresh the worksheet or rerun the command; Snowflake does not auto-refresh browser tabs after DDL operations.

FAQs

Does SHOW DATABASES consume credits?

Only the minimal compute to start a warehouse. Query duration is short, so cost is negligible on an X-SMALL warehouse.

Can I list databases without a warehouse?

No. An active warehouse is required even for metadata queries.

How do I export the list to a table?

Use CREATE TABLE db_inv AS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())); right after running the command.

Why How to List Databases in Snowflake is important

How to List Databases in Snowflake Example Usage


-- List only databases related to the ecommerce stack
SHOW DATABASES LIKE 'ECOM%';

-- Save result to an audit table
CREATE OR REPLACE TABLE admin.db_inventory AS
SELECT *
FROM   TABLE(RESULT_SCAN(LAST_QUERY_ID()));

How to List Databases in Snowflake Syntax


SHOW [TERSE] DATABASES [ LIKE '<pattern>' | STARTS WITH '<prefix>' ] [ LIMIT <row_count> ];

-- E-commerce context example
-- Assume databases: ECOMMERCE, ANALYTICS, FINANCE
SHOW TERSE DATABASES LIKE 'ECOM%';

Common Mistakes

Forgetting to activate a warehouse: SHOW DATABASES needs an active warehouse session even though it reads only metadata. Resume a tiny warehouse to avoid failure.
Running SHOW DATABASES without filters in large accounts: You may hit the 10,000-row limit and miss databases. Always use LIKE or STARTS WITH when many databases exist.