title: How to create a dependency list for an object in Redshift?
body: <p>My team has views built upon views built upon views, so often a <code>DROP TABLE CASCADE</code> is a recipe for disaster and a lot of trial and error.</p>
  
  <p>What I would like is a query that returns all dependent objects that need to be re-created in the correct order given a certain <code>schema</code> and <code>table</code> so that they could be automated and ran in a script.  I'm working with a modified version of the dependency query on the Redshift <code>DROP TABLE</code> documentation <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html" rel="noreferrer">http://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html</a>.</p>
  
  <p>It seems to be returning views and their dependencies, but not regular tables.  I feel like I'm close, what am I missing?</p>
  
  <pre><code>WITH dependencies AS (
      SELECT DISTINCT
        cls1.oid     AS tbloid,
        nsp1.nspname AS schemaname,
        cls1.relname AS name,
        nsp2.nspname AS refbyschemaname,
        cls2.relname AS refbyname,
        cls2.oid     AS viewoid
      FROM pg_catalog.pg_class cls1
        JOIN pg_catalog.pg_depend dep1
          ON cls1.relfilenode = dep1.refobjid
        JOIN pg_catalog.pg_depend dep2
          ON dep1.objid = dep2.objid
        JOIN pg_catalog.pg_class cls2
          ON dep2.refobjid = cls2.relfilenode
        LEFT OUTER JOIN pg_namespace nsp1
          ON cls1.relnamespace = nsp1.oid
        LEFT OUTER JOIN pg_namespace nsp2
          ON cls2.relnamespace = nsp2.oid
      WHERE dep2.deptype IN ('i' :: "CHAR", 'n' :: "CHAR")
            AND cls2.relkind IN ('v' :: "CHAR", 'r' :: "CHAR")
            AND nsp1.nspname NOT IN ('pg_catalog', 'information_schema')
      ORDER BY 4, 5
  ),
  
      joined_to_views AS (
        SELECT
          d.schemaname,
          d.name,
          d.refbyschemaname,
          d.refbyname,
          p.definition
        FROM dependencies d
          LEFT JOIN pg_views p
            ON d.refbyschemaname = p.schemaname AND d.refbyname = p.viewname
    )
  
  SELECT *
  FROM joined_to_views
  </code></pre>