is there way list of tables truncated truncate cascade
in postgres?
so example, assuming have 3 tables:
a b (depends on a) c (depends on b)
truncate cascade;
truncate b
, c
. how check ahead of time?
with of this answer can foreign table name query
select tc.constraint_name ,tc.table_name ,kcu.column_name ,ccu.table_name foreign_table_name ,ccu.column_name foreign_column_name information_schema.table_constraints tc join information_schema.key_column_usage kcu on tc.constraint_name = kcu.constraint_name join information_schema.constraint_column_usage ccu on ccu.constraint_name = tc.constraint_name constraint_type = 'foreign key' , ccu.table_name = 'a'
or
create view query
create view vdepend_table select s1.constraint_name ,s1.table_name ,s1.column_name ,s1.ordinal_position ,s2.table_name_ref ,s2.column_name_ref ,s2.ordinal_position_ref ( select key_column_usage.constraint_name ,key_column_usage.table_name ,key_column_usage.column_name ,columns.ordinal_position information_schema.key_column_usage join information_schema.columns using ( table_name ,column_name ) ) s1 join ( select constraint_column_usage.constraint_name ,constraint_column_usage.table_name table_name_ref ,constraint_column_usage.column_name column_name_ref ,cols_ref.ordinal_position ordinal_position_ref information_schema.constraint_column_usage join information_schema.columns cols_ref on cols_ref.table_name::text = constraint_column_usage.table_name::text , cols_ref.column_name::text = constraint_column_usage.column_name::text ) s2 on s1.constraint_name::text = s2.constraint_name::text , not s1.table_name::text = s2.table_name_ref::text;
usage:
select table_name vdepend_table table_name_ref='a'
Comments
Post a Comment