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)

as

(

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

)t

where rn=1

order by level

sp_fkeys v/s sp_foreignkeys

sp_fkeys and sp_foreignkeys are two system procedures available in SQL Server giving information about foreignkeys. Then what exactly is difference between them? For this lets see the syntax of both.

sp_fkeys
---------------
sp_fkeys @pktable_name,     @pktable_owner,     @pktable_qualifier,     @fktable_name,     @fktable_owner,     @fktable_qualifier 
If you see from above sp_fkeys accept the above parameters and returns the  information on foreign keys.  All parameters are optional. If you pass only  the pk table information, it lists all the tables where this pk column   is used as a foreign key. Alternatively if you pass only fk table  information it returns details of all tables related  to passed table in  pk fk relationship
Now lets see the syntax for sp_foreignkeys
sp_foreignkeys  @table_server,    @pktab_name,     @pktab_schema,    @pktab_catalog ,    @fktab_name,    @fktab_schema ,    @fktab_catalog 
As you see from above the first parameter is table server name.  This is  because sp_foreignkeys retrieves details of foreign key  relationships  from linked server. so we need to pass the linked server name as first  parameter for sp_foreignkeys. the other parameters have exactly same  meaning as in sp_fkeys.Similarly sp_pkeys and sp_primarykeys procedures differ in same way.