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;