Finding Redshift Dependencies

Dropping a table in Redshift also requires dropping all associated views that reference that table. Although it's possible to automatically drop all associated views using the CASCADE command, that's a rather blunt instrument, and you may accidentally drop views you don't want to.

If you want to determine all of the views that depend on a table, you can run the following query, swapping in the name of the table you're interested in for 'your_table_name_here':

SELECT DISTINCT 
 srcobj.oid AS src_oid
 ,srcnsp.nspname AS src_schemaname
 ,srcobj.relname AS src_objectname
 ,tgtobj.oid AS dependent_viewoid
 ,tgtnsp.nspname AS dependent_schemaname
 ,tgtobj.relname AS dependent_objectname
FROM
 pg_catalog.pg_class AS srcobj
INNER JOIN
 pg_catalog.pg_depend AS srcdep
 ON srcobj.oid = srcdep.refobjid
INNER JOIN
 pg_catalog.pg_depend AS tgtdep
 ON srcdep.objid = tgtdep.objid
JOIN
 pg_catalog.pg_class AS tgtobj
 ON tgtdep.refobjid = tgtobj.oid
 AND srcobj.oid <> tgtobj.oid
LEFT OUTER JOIN
 pg_catalog.pg_namespace AS srcnsp
 ON srcobj.relnamespace = srcnsp.oid
LEFT OUTER JOIN
 pg_catalog.pg_namespace tgtnsp
 ON tgtobj.relnamespace = tgtnsp.oid
WHERE tgtdep.deptype = 'i'
AND tgtobj.relkind = 'v'
AND srcobj.relname = '<your_table_name_here>'

If you'd like to see the list of all table-view dependencies on your cluster, modify the query by removing the last line.