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.with cteFK (pktable, fktable) as ( select pktable = s1.name + '.' + o1.name , fktable = isnull(s2.name + '.' + o2.name, '') from sys.objects o1 left outer join sys.sysforeignkeys fk on o1.object_id = fk.fkeyid left outer join sys.objects o2 on o2.object_id = fk.rkeyid left outer join sys.schemas s1 on o1.schema_id = s1.schema_id left outer join sys.schemas s2 on o2.schema_id = s2.schema_id where o1.type_desc = 'user_table' and o1.name not in ('dtproperties','sysdiagrams') group by s1.name + '.' + o1.name , isnull(s2.name + '.' + 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 and cteFK.pktable <> 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 InsertOrder asc, TableName asc
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,
... or drop the tables 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
UPDATED 20140507: changed old system reference objects (sysobjects) to new system reference objects (sys.objects) UPDATED 20140624: added "and cteFK.pktable <> cteRec.tablename", see comments for explanation.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
I get "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
My database only has 24 tables in it.
Ah, I had a table with a FK to itself. This and other loops will kill your script ... of course, there's no easy way to say how to actually do an insert if there is a loop.
Actually, adding the line "and cteFK.pktable <> cteRec.tablename" seemed to clear up that case. Thanks for commenting!
Post a Comment