List all tables in snowflake schema

To Nha Notes | July 20, 2022, 2:25 p.m.

To list tables in a snowflake schema, execute below SQL;

USE <DATABASE>;
SELECT
    TABLE_SCHEMA
,   TABLE_NAME
,   CREATED         AS  CREATE_DATE
,   LAST_ALTERED    AS  MODIFY_DATE
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_TYPE      =       'BASE TABLE'
AND TABLE_SCHEMA    LIKE    '%<SCHEMA>%'
ORDER BY
    TABLE_SCHEMA
,   TABLE_NAME
;

To generate list of SQLs to drop bunch of tables in snowflake, execute below SQLs:

USE <DATABASE>;
SELECT
    CONCAT('DROP TABLE <DATABASE>.', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_TYPE      =       'BASE TABLE'
AND TABLE_SCHEMA    LIKE    '%<SCHEMA>%'
ORDER BY
    TABLE_SCHEMA
,   TABLE_NAME;