Wednesday, November 9, 2011

Recursive delete from parent child tables

Quite often there are cases where we will have set of tables linked by means of foreign key relationships. Deleting from one among such tables can be a real pain especially when it having lots of direct and indirect dependencies on child tables. In such cases, we need to start deleting dependent records from child tables back to parent recursively. The below code will help us in finding out recursively the object relationships and then delete from tables the dependent records

;with object_cte(tblid,tblname,rtblid,rtblname,level)
select distinct o.object_id as tblid,OBJECT_NAME(o.object_id) as tblname,cast(null as int),cast(null as sysname),
0 as level
from sys.objects o
inner join sys.foreign_keys f
on f.parent_object_id = o.object_id
where o.is_ms_shipped=0
and o.type='u'
union all
select t.object_id as tblid,OBJECT_NAME(t.object_id) as tblname,o.tblid,o.tblname,o.level + 1
from object_cte o
inner join sys.foreign_keys f
on f.parent_object_id = o.tblid
join sys.objects t
on t.object_id = f.referenced_object_id
where t.is_ms_shipped=0
and t.type='u'

select * from
select row_Number() over (partition by tblname order by level) as rn,* from object_cte
where rn=1
order by level