Printed 2017/02/21 11:46PM

Using Foreign Keys to Determine Table Insertion Order

By, 2013/02/17

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

with cteFK (pktable, fktable) as ( 
            pktable = 
       ,    fktable = isnull(, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on = fk.fkeyid        
       left join sysobjects o2 on = fk.rkeyid        
       where o1.xtype = 'u'        
       and not in ('dtproperties','sysdiagrams')        
       group by,  isnull(, '')    
), cteRec (tablename, fkcount) as  ( 
       select tablename = pktable 
       ,    fkcount = 0
       from cteFK       
       UNION ALL        
       select tablename = pktable 
       , fkcount = 1
       from cteFK  
       cross apply cteRec        
       where cteFK.fktable = cteRec.tablename    
, InsertOrder = dense_rank() OVER ( ORDER BY max(fkcount) asc )
from (       
              tablename = fktable
       ,      fkcount = 0 
       from cteFK 
       group by fktable    
       UNION ALL     

       select tablename = tablename, fkcount = sum(ISNULL(fkcount,0))   
       from cteRec      
       group by tablename
     ) x 
where x.tablename <> '' 
group by tablename 
order by 2,1 asc
Use the sample script from the previous post on how to "Script Out Foreign Keys With Multiple Keys" for an example of building a complicated set of foreign key relationships to test this script out.

Here's the results from that example:

Similarly, this script would generate an order for you to unravel the data - the numbers descending would allow you to delete in the proper order,
delete from fktable11
delete from fktable10
delete from fktable9
delete from fktable8
delete from fktable6
delete from fktable4
delete from fktable2
delete from fktable7
delete from fktable5
delete from fktable3
delete from fktable1
... or drop the tables in the proper order.
drop table fktable11
drop table fktable10
drop table fktable9
drop table fktable8
drop table fktable6
drop table fktable4
drop table fktable2
drop table fktable7
drop table fktable5
drop table fktable3
drop table fktable1

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.