I am using AWS Redshift. I have more than 100 tables in one schema, out of which the data of some tables get refreshed on a daily basis while some tables do not get refreshed at all. I have figured out the tables whose data do not get refreshed. Before having them dropped, I want to check if they are currently in use by any function/procedure/view of the same schema or any other schema. Is there any way to check this out on Redshift?
1 Answers
I found this code online, don't remember where so I am sorry I can't credit...
I recommend saving this as a view so you can always run this easily.
And if you have views that use certain tables that you want to drop, you can use WITH NO SCHEMA BINDING at the end of the view. That way the table will be able to be dropped even if there is a view using it.
The code:
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' --dependency_internal
AND tgtobj.relkind = 'v' --i=index, v=view, s=sequence
and src_schemaname <> 'pg_catalog' and src_schemaname <> 'information_schema';
Related