When deleting table in Redshift I get an error message :
Amazon Invalid operation: cannot drop table rawdata.bss_edw_customer_account because other objects depend on it;
I don't want to use CASCADE because I don't know which other tables/views it will kill. How do I find out which tables or views are dependent on the table I want to drop? I want to make sure I don't step on somebody else's work.
Thanks
Redshift Admin Views for object [1] and constraint [2] dependencies can help in identifying dependent objects. You can also create the find_depend view as described in the DROP table documentation [3] to view any dependencies.
I also found that these views may not always list materialized views as dependent objects. If you have created any MV in the past, then you might want to check for dependencies using the view DDLs. The following query could help:
select schemaname, viewname from pg_views where schemaname not like 'pg_catalog' and schemaname not like 'information_schema' and definition like '%<tablename>%';
[1] https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_object_dependency.sql
[3] https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html
If you need a more general overview of the (materialized) view dependencies in a schema you can use the following snippit. Just replace schema_name with the name of the schema. If you need to display dependencies across schemas this should be easily doable by adapting the regex or by doing a union.
create view admin.v_view_dependencies_fixed as (
with h1 as (
select generate_series as i
from generate_series(1, 100) -- we can use this since the query only touches the leader node
),
h2 as (
select schemaname as dependent_schema,
viewname as dependent_view,
schemaname || '.' || viewname as dependent_full,
regexp_substr(definition, 'schema_name\\.\\w+', 1, i) as dependency
from pg_views
cross join h1
where schemaname = 'schema_name'
and dependency is not null
and dependency != ''
)
select distinct
dependent_full,
dependent_schema,
dependent_view,
dependency as source_full,
split_part(dependency, '.', 1) as source_schema,
split_part(dependency, '.', 2) as source_object
from h2
where dependent_full != source_full
);