select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_names
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
--and t.relname like 'test%'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;
SELECT
tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE tc.table_name = 'xyz' AND tc.constraint_schema = 'abc'
SELECT
*
FROM
information_schema.triggers
WHERE
trigger_schema = 'abc'
SELECT
CONCAT('DROP TRIGGER IF EXISTS ',trigger_name,' ON ',trigger_schema,'.',event_object_table,';') AS sql
FROM
information_schema.triggers
WHERE
trigger_schema = 'abc'
SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = 'abc'
AND
table_type = 'BASE TABLE'
Models are where your developers spend most of their time within a dbt environment. Models are primarily written as a select statement and ...