SHOW commands list objects: SHOW TABLES IN SCHEMA raw, SHOW SCHEMAS, SHOW WAREHOUSES, SHOW GRANTS TO ROLE analyst. Add LIKE 'ORD%' to filter by name. DESCRIBE TABLE (or DESC TABLE) lists a table's columns and types.
SHOW isn't a query, so you can't add WHERE to it. Snowflake's answer is RESULT_SCAN, which reads the result of an earlier statement as a table:
SHOW TABLES IN SCHEMA sales;
SELECT "name", "rows"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "rows" > 100;
SHOW's columns have lowercase names, so quote them.
Each database also has an INFORMATION_SCHEMA with views you can query directly, such as TABLES and COLUMNS. They show only what your role can see.