How to List Tables in Snowflake

Galaxy Glossary

How do I list tables in Snowflake?

SHOW TABLES and information-schema queries reveal all tables in a Snowflake account, database, or schema.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I quickly list every table in my current schema?

Run SHOW TABLES;. The result grid displays table names, database, schema, row counts, owners, and creation timestamps.

How can I list tables in a specific database or schema?

Qualify the command: SHOW TABLES IN SCHEMA ecommerce.public;. If the database is not your current one, add it explicitly.

What if I need pattern matching?

Filter with LIKE or STARTS WITH: SHOW TABLES LIKE 'prod%'; or SHOW TABLES STARTS WITH 'ord';.Both clauses are case-insensitive unless quoted identifiers are used.

How do I see only permanent, transient, or temporary tables?

Append WITH TYPE = 'PERMANENT', 'TRANSIENT', or 'TEMP' after the command: SHOW TABLES WITH TYPE = 'TRANSIENT';.

Can I query the results instead of scanning the grid?

Yes.Capture the output into a Snowflake result table and query it:
SHOW TABLES IN SCHEMA ecommerce.public; SELECT "name", "rows" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) ORDER BY rows DESC;

Best practice: use information_schema for automation

For scripts, query INFORMATION_SCHEMA.TABLES:
SELECT table_name FROM ecommerce.public.INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE';.
This avoids parsing SHOW output.

Why use SHOW over information_schema?

SHOW returns rich metadata (cluster keys, comments) and supports pattern filters.INFORMATION_SCHEMA is ANSI-style SQL and better for joins.

How do I list tables across all schemas?

Loop through schemas:
SHOW SCHEMAS IN DATABASE ecommerce; SELECT listagg('SHOW TABLES IN SCHEMA '||"name"||';','\n') FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));. Then execute the generated statements.

Why How to List Tables in Snowflake is important

How to List Tables in Snowflake Example Usage


-- List all product-related tables in the ecommerce.public schema
SHOW TABLES IN SCHEMA ecommerce.public LIKE 'products%';
-- Query the output for row counts
SELECT name, rows
FROM   TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER  BY rows DESC;

How to List Tables in Snowflake Syntax


SHOW TABLES [IN { ACCOUNT | DATABASE | SCHEMA <db_name>.<schema_name> }] [LIKE '<pattern>'] [STARTS WITH '<prefix>'] [WITH TYPE = { 'PERMANENT' | 'TRANSIENT' | 'TEMP' }] [LIMIT <n>];

Common Mistakes

Using SHOW TABLES without a qualified schema and then misinterpreting cross-schema results. Always add IN SCHEMA when you only need one schema.
Quoting patterns incorrectly—single quotes are required around LIKE or STARTS WITH strings. Missing quotes triggers a syntax error.