July 17, 2004 at 10:39 am
[Reposted from a different forum where I wasn't getting much response - hope thats OK!!!]
Hi,
Interesting problem here.
We are building an app that imports data from a VERY shoddy source. There is no RI in the source and if we tried to enforce RI it would fail miserably due to there being values in tableA that are not present in tableB when logically speaking there should be - I think you get the picture. Our destination system has proper RI so what we want to do is dump all the bad data (i.e. Anything that would violate an FK constraint) during import into "buckets" so they it be examined later. The source system has upwards of 50 tables.
In order to do this we need to work out what I am calling (for the purposes of this post) the table hierarchy. i.e. A logical ordering of the tables that places the table(s) with no supposed FKs to other tables at the "top" of the hierarchy. Thus we are building a model that shows where our transitive dependancies (e.g. TableA references TableB which references TableC therefore TableC is at the top of our hierarchy) lie.
Deleting everything from TableA that isn't in TableB is easy enough but later we may delete something from TableB because it isn't in TableC and now we have the possibility of records in TableA that will fail RI.
SO, what we need is to be able to calculate an order in which we should delete data from tables and move it into the buckets. [In the simple example here in the order would be to delete from TableB then TableA.]
Yes, we could do this manually but we would like a piece of code that calculates this order for us. This would rule out the possibility of errors arising by working out the order manually and also give us a bit of code that we could take anywhere that this may arise again.
Has anyone got a bit of code that will do this? Ideally we'd write a recursive bit of code that looked at sysdepends but I think I'm right in saying that sysdepends can't be trusted.
I hope I have explained this thoroughly enough. I would appreciate any help that anyone could offer.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 17, 2004 at 8:23 pm
I never use sysdepends, sometimes it cannot be trusted.
This script uses sysreferences instead.
And gives the "nestlevel" of the tables. ie the no of tables above a particular table in the FK hierarchy.
set nocount on
-- create temporary table for fk nesting
if object_id('tempdb..#nesting') is not null drop table #nesting
create table #nesting( id int primary key, nestlevel int )
-- Initiate nesttable with nonreferenced tables
insert #nesting( id, nestlevel )
select so.id, 0 from sysobjects so where so.type = 'U'
and not exists( select * from sysreferences sr where sr.fkeyid = so.id and sr.fkeyid sr.rkeyid )
-- Insert all the nested tables
while @@rowcount > 0
insert #nesting( id, nestlevel )
select distinct sr.fkeyid, #nesting.nestlevel + 1
from #nesting join sysreferences sr on #nesting.id = sr.rkeyid
where #nesting.nestlevel = ( select max(nestlevel) from #nesting )
and sr.fkeyid not in( select id from #nesting )-- recursive relationships
-- Display results
select su.name as [schema], so.name as
, #nesting.nestlevel
from #nesting join sysobjects so on #nesting.id = so.id
join sysusers su on so.uid = su.uid
order by #nesting.nestlevel, so.name
/rockmoose
You must unlearn what You have learnt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply