Snowflake - Query to check if given schema is empty i.e. no object in schema

Question

I am trying to create single query which can tell if given snowflake schema is empty so that it can be dropped.

I have few SQLs created which queries the INFORMATION_SCHEMA views - tables, views, sequences to check if any object created. In this way I have to create multiple queries which returns flag values if it has table/view in given schema. This query becomes lengthy and multiple hits.

I am looking for single query which can tell me if given schema is empty?

Lukasz Szozda · Answer 1 · 2021-04-29T09:56:51.970

I am looking for single query which can tell me if given schema is empty?

You could write single query that search in INFORMATION_SCHEMA:

SET schema_name = 'MY_SCHEMA';

SELECT $schema_name AS schema_name,
   CASE WHEN EXISTS (
      SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ILIKE $schema_name
      UNION ALL
      SELECT 1 FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA ILIKE $schema_name
      UNION ALL
      -- all information_schema tables tasks/sequences/functions/streams/...
     ) THEN 0
       ELSE 1
END is_empty;

Alternatively using SHOW:

SHOW TABLES IN SCHEMA 'schema_name';
SHOW TASKS IN SCHEMA 'schema_name;
-- ... all SHOW commands

SELECT 1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
UNION ALL
SELECT 1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-2)))
...
-- if returns 1 it means that at least one SHOW command returned rows
-- therefore it contains object in schema

Warning!

Both approaches may return false negative as you are able to see only objects you have access to.

Snowflake - Query to check if given schema is empty i.e. no object in schema

1 Answers1