DROP Commands

-- Drop a materialized view
DROP MATERIALIZED VIEW my_view;

-- Drop a source
DROP SOURCE my_source;

-- Drop a cluster
DROP CLUSTER my_cluster;

-- Drop with CASCADE (removes dependent objects)
DROP MATERIALIZED VIEW my_view CASCADE;

-- Drop with RESTRICT (fails if dependencies exist) - default behavior
DROP MATERIALIZED VIEW my_view RESTRICT;

-- Safely drop if exists (no error if doesn't exist)
DROP MATERIALIZED VIEW IF EXISTS my_view;

DROP SOURCE IF EXISTS my_source CASCADE;

-- Find objects that depend on a view
SELECT
    dependent.name AS dependent_object,
    dependent.type AS object_type
FROM mz_catalog.mz_objects dependent
JOIN mz_internal.mz_object_dependencies dep ON dependent.id = dep.object_id
JOIN mz_catalog.mz_objects target ON dep.referenced_object_id = target.id
WHERE target.name = 'my_view';

-- Find all dependencies recursively
WITH RECURSIVE deps AS (
    SELECT id, name
    FROM mz_catalog.mz_objects
    WHERE name = 'my_view'
    UNION
    SELECT o.id, o.name
    FROM mz_catalog.mz_objects o
    JOIN mz_internal.mz_object_dependencies d ON o.id = d.object_id
    JOIN deps ON d.referenced_object_id = deps.id
)
SELECT * FROM deps;

-- Drop test views
DROP MATERIALIZED VIEW IF EXISTS test_mv CASCADE;
DROP VIEW IF EXISTS test_view;

-- Drop test sources
DROP SOURCE IF EXISTS test_source CASCADE;

-- First, check what's running on the cluster
SELECT name, type
FROM mz_catalog.mz_objects
WHERE cluster_id = (
    SELECT id FROM mz_catalog.mz_clusters WHERE name = 'old_cluster'
);

-- Drop the cluster (will fail if objects still use it)
DROP CLUSTER old_cluster;

-- Drop entire hierarchy
DROP MATERIALIZED VIEW top_level_view CASCADE;
-- This will also drop all views, indexes, and sinks that depend on it

-- List objects in schema first
SELECT name, type FROM mz_catalog.mz_objects
WHERE schema_id = (SELECT id FROM mz_catalog.mz_schemas WHERE name = 'old_schema');

-- Drop schema and all contents
DROP SCHEMA old_schema CASCADE;

-- Drop all objects with 'test_' prefix
DROP MATERIALIZED VIEW IF EXISTS test_orders CASCADE;
DROP SOURCE IF EXISTS test_kafka_source CASCADE;
DROP CLUSTER IF EXISTS test_cluster CASCADE;