pointers, solutions and scripts for the SQL DBA
Not intended to replace common sense

2/19/2013

Using Foreign Keys to Determine Table Insertion Order

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 ( 
       select              
            pktable = o1.name 
       ,    fktable = isnull(o2.name, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on o1.id = fk.fkeyid        
       left join sysobjects o2 on o2.id = fk.rkeyid        
       where o1.xtype = 'u'        
       and o1.name not in ('dtproperties','sysdiagrams')        
       group by o1.name,  isnull(o2.name, '')    
), 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    
) 
select 
  TableName
, InsertOrder = dense_rank() OVER ( ORDER BY max(fkcount) asc )
from (       
       select 
              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

No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.