How to List Tables in Snowflake

SHOW TABLES returns rows but not a result set you can filter with WHERE. To query its output, follow it with SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())). It is metadata-only, so it needs no running warehouse and costs nothing, which is a real advantage over information_schema.

SHOW TABLES;
SHOW TABLES LIKE 'order%' IN SCHEMA analytics.public;

Query information_schema

information_schema.tables is a normal view you can filter and join, scoped to whichever database you qualify it with. Identifiers are stored uppercase unless they were created quoted, which is why the filter reads 'PUBLIC' and not 'public'. row_count and bytes are maintained by Snowflake and are exact for regular tables, NULL for views.

SELECT table_name, row_count, bytes
FROM analytics.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY bytes DESC;

SHOW TABLES and INFORMATION_SCHEMA answer different questions

SHOW TABLES is metadata-only, needs no running warehouse, and returns row counts and byte sizes for free. INFORMATION_SCHEMA.TABLES is a real view you can join and filter in SQL, but it only covers the current database and requires a warehouse. SHOW also caps its output, so pipe it through RESULT_SCAN when you need to filter a large result.

SHOW TABLES IN SCHEMA analytics.public;

SELECT "name", "rows", "bytes"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "rows" > 1000000;

Identifiers fold to upper case

An unquoted name is stored upper case, so a filter on table_name = 'orders' returns nothing while 'ORDERS' works. A table created as "orders" with quotes really is lower case and can only be referenced with quotes. Comparing with UPPER on both sides avoids having to know which happened.

SELECT table_name FROM information_schema.tables
WHERE UPPER(table_name) = UPPER('orders');

Looking across every database

INFORMATION_SCHEMA stops at the current database, which is why a search for a table you know exists can come back empty. SNOWFLAKE.ACCOUNT_USAGE.TABLES spans the whole account, at the cost of a latency of up to 90 minutes and a soft-deleted row that lingers until DELETED is set. Filter on DELETED IS NULL or you will find tables that are already gone.

SELECT table_catalog, table_schema, table_name, row_count
FROM snowflake.account_usage.tables
WHERE deleted IS NULL AND table_name = 'ORDERS';