Get list of tables that would be truncated by a cascade

Question

Is there a way to get a list of tables that would also be truncated by a TRUNCATE CASCADE in postgres?

So for example, assuming we have three tables:

a
b (depends on a)
c (depends on b)

TRUNCATE a CASCADE; would also truncate b and c. How could we check this ahead of time?

Works like a charm! I would just add for those searching that you can add `AND ccu.table_name = 'a'` to satisfy the question. — Leah Sapan, Jan 29 '16 at 05:32

score 1 · Accepted Answer · edited May 23 '17 at 12:30

With the help of this answer you can get the foreign table name by this query

SELECT tc.constraint_name
      ,tc.table_name
      ,kcu.column_name
      ,ccu.table_name AS foreign_table_name
      ,ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'a'

OR

Create a view with another query

CREATE VIEW vdepend_table
AS
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
FROM (
    SELECT key_column_usage.constraint_name
        ,key_column_usage.table_name
        ,key_column_usage.column_name
        ,columns.ordinal_position
    FROM 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 AS table_name_ref
        ,constraint_column_usage.column_name AS column_name_ref
        ,cols_ref.ordinal_position AS ordinal_position_ref
    FROM information_schema.constraint_column_usage
    JOIN information_schema.columns cols_ref ON cols_ref.table_name::TEXT = constraint_column_usage.table_name::TEXT
        AND cols_ref.column_name::TEXT = constraint_column_usage.column_name::TEXT
    ) s2 ON s1.constraint_name::TEXT = s2.constraint_name::TEXT
    AND NOT s1.table_name::TEXT = s2.table_name_ref::TEXT;

usage:

 select table_name from vdepend_table where table_name_ref='a'

Get list of tables that would be truncated by a cascade

1 Answers1