Handling Relational Data

Handling Relational Data In Relational Databases
Managing relational databases is sometimes tedious, especially when we have to upload the data into the relational schemas or delete one particular row from a table as since it’s relational, the referential integrity wont allows the parent table to delete unless the related rows delete from the foreign keys. The chain branches of parent tables are simple or complex as one node might have multiple branches.
The below procedure will give the tree structure, so the output of this procedure you can use to delete or insert or update the keys (especially in case of data masking). All you have to do dump the output of this stored procedure in to temp table and generate the generic queries or do it manually, if it’s simple.

create  procedure [dbo].[pr_schema] as 
set nocount on 

SELECT SCHEMA_NAME(SOF.SCHEMA_ID) AS FSC_NAME,T1.FKEYID AS FK,SCHEMA_NAME(SOR.SCHEMA_ID) AS RSC_NAME,T1.RKEYID AS PK 
into #sysref FROM sysforeignkeys T1 
left outer JOIN sys.objects SOF ON SOF.OBJECT_ID = T1.FKEYID 
left outer JOIN sys.objects SOR ON SOR.OBJECT_ID = T1.RKEYID 
-- below 3 lines is to avoid the problems with self reference 
LEFT OUTER JOIN SYSREFERENCES T2 
ON OBJECT_NAME(T1.FKEYID)+OBJECT_NAME(T1.RKEYID) = OBJECT_NAME(T2.RKEYID)+OBJECT_NAME(T2.FKEYID) 
WHERE T2.FKEYID IS NULL 
--******************************************************* 
-- Get the Max No.Of Joins foreign keys in a Chain 
declare @N VARCHAR(9); 
WITH CTE(FKEYID,LEVEL) 
    AS 
     (SELECT FKEYID,0 FROM sysforeignkeys 
    UNION ALL 
    select T1.PK ,LEVEL+1 FROM #sysref T1 JOIN CTE T2 ON T1.FK = T2.FKEYID ) 
-- Statement that executes the CTE 
SELECT @N = Max(level)  FROM CTE 
PRINT @N 
--******************************************************* 
Declare @V1 varchar(max) 
set @V1 = '' 
  
Declare @V2 varchar(max) 
set @V2 = '' 
declare @select varchar(max) 
set @select = '' 
Declare @OrderBy varchar(2) 
set  @OrderBy = @N+1 
WHILE NOT @N = 0 
BEGIN 
             set @V1 =  @V1 + ' right outer join #sysref A' +cast(@N as varchar(4)) + ' on a'+ @V2+'.pk =A'+cast(@N as varchar(4))+'.fk' 
             set @select =  @select+    'a'+ @N+'.FSC_NAME+''.''+object_name('+'a'+ @N+'.fk),' 
set @V2 = @N 
SET @N = @N -1 
CONTINUE 
END 
SET @select=  @select +  'A1.RSC_NAME+''.''+object_name(A1.PK)' 
exec('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy ) 
print ('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy )