Sort table names by foreign key dependencies in a schema/Database

rkartheek · #1 (**permalink**) 09-03-09, 22:22

I want to generate a list of table names into a file in the order of FK dependencies. The purpose is to truncate all tables in a given database/schema by using the generated file in a "for loop". I use "Load from /dev/null..." or "Import.." command to truncate the tables. The database is IBM DB2 V 9 on an AIX platform. The below query gave me a list of tables that aren't parents, in other words list of Children that aren't parents. I need help in taking this further up the hierarchy. Thanks in advance for the help.

db2 "WITH tmp
(
tabname
) AS
(SELECT DISTINCT rtrim(reftabschema) || '.' || SUBSTR(reftabname,1,50)
FROM syscat.references
ORDER BY 1
)
SELECT distinct rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
FROM syscat.references
WHERE rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
NOT IN (select * FROM tmp)"|awk '{print $1}'

ARWinner · #2 (**permalink**) 09-04-09, 09:01

I posted this somewhere before, but I could not find it.

Code:

with temp1 as (
select distinct t.tabname,nullif(r.reftabname,t.tabname) as reftable from syscat.tables as t 
left outer join syscat.references as r on t.tabschema = r.tabschema and t.tabname = r.tabname 
where t.tabschema = 'MySchema' and t.type = 'T' )  , 
temp2 (table, reftable,lvl) as (
select tabname,reftable,1 from temp1 where reftable is null and 
tabname not in (select tabname from temp1 where reftable is not null) 
union all 
select t.tabname,t.reftable,z.lvl+1 from temp1 as t, temp2 as z where t.reftable = z.table
) select table,max(lvl) as mlevel from temp2 as t 
inner join syscat.tables as w on (t.table = w.tabname and w.tabschema = 'MySchema') 
group by table order by mlevel,table

Be careful with this since it is recursive and if you have a circular referencing scheme, it will never return. It does handle if a table references itself though.

Andy

rkartheek · #3 (**permalink**) 09-04-09, 21:08

Thanks very much Andy, It worked.

Internet Services

Web Development

Digital Marketing

Consumer Tech