cascade delete - Deleting the foreign keys tables without giving the child table names.

Have you ever faced a situation where you need to delete all your master table data along with its corresponding transaction table records. I faced few days ago and got a solution to delete the records with foreign keys references. The below code worked for me for cascade delete, if its not working for you pls let me know with the error u get.


declare  @cTab varchar(255),  
 @cCol varchar(255),  
 @cRefTab varchar(255),  
 @cRefCol varchar(255),  
 @cFKName varchar(255),  
 @cSQL nvarchar(1000),  
 @cChildCriteria nvarchar(1000),  
 @iChildRows int  

 
DECLARE cFKey CURSOR LOCAL FOR  
SELECT SO1.name AS Tab,  
       SC1.name AS Col,
       SO2.name AS RefTab,  
       SC2.name AS RefCol,  
       FO.name AS FKName  
FROM dbo.sysforeignkeys FK    
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id  
                              AND FK.fkey = SC1.colid  
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id  
                              AND FK.rkey = SC2.colid  
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id  
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id  
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id  
WHERE SO2.Name = @cTableName  

OPEN cFKey  
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
WHILE @@FETCH_STATUS = 0  
     BEGIN  
   SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +  
           @cRefTab +'] WHERE ' + @cCriteria + ')'  

     
   EXEC [sp_CascadeDelete] @cTab, @cChildCriteria  
   FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
     END  
Close cFKey
DeAllocate cFKey  

SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria  
EXEC sp_ExecuteSQL @cSQL