-
Notifications
You must be signed in to change notification settings - Fork 42
/
redshift_admin_dependencies.sql
25 lines (21 loc) · 924 Bytes
/
redshift_admin_dependencies.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{# SOURCE: https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_view_dependency.sql #}
select distinct
srcobj.oid as source_oid
, srcnsp.nspname as source_schemaname
, srcobj.relname as source_objectname
, tgtobj.oid as dependent_oid
, tgtnsp.nspname as dependent_schemaname
, tgtobj.relname as dependent_objectname
from
{{ ref('pg_class') }} as srcobj
join {{ ref('pg_depend') }} as srcdep on srcobj.oid = srcdep.refobjid
join {{ ref('pg_depend') }} as tgtdep on srcdep.objid = tgtdep.objid
join {{ ref('pg_class') }} as tgtobj
on tgtdep.refobjid = tgtobj.oid
and srcobj.oid <> tgtobj.oid
left join {{ ref('pg_namespace') }} as srcnsp
on srcobj.relnamespace = srcnsp.oid
left join {{ ref('pg_namespace') }} tgtnsp on tgtobj.relnamespace = tgtnsp.oid
where
tgtdep.deptype = 'i' --dependency_internal
and tgtobj.relkind = 'v' --i=index, v=view, s=sequence