Find records deleted by cascade delete

Question

Environment : SQL Server 2017, Windows 10, Visual Studio 2019 C++

Hi All,

When I delete a record from a table, few record are getting deleted from some other table due to cascade delete relationship. I want to know records deleted by cascade delete. Is there any way to find out those records?

Thanks in advance.

Regards,
Vijay Kumar Singh

Answer 1

Do you know the column and value has been deleted, run
SELECT C.*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
WHERE DELETE_RULE = 'CASCADE'

Answer 2

Hi @Vijay Singh

When I delete a record from a table, few record are getting deleted from some other table due to cascade delete relationship. I want to know records deleted by cascade >delete. Is there any way to find out those records?

Yes. You can use DBCC LOG to find the records, try code as next:

DBCC TRACEON(3604)  
GO  
DBCC LOG(dbname,3)  
GO  

Or

--2.FIND THE DELETED ONES  
SELECT   
 [Current LSN],      
 [Transaction ID],  
     Operation,  
     Context,  
     AllocUnitName  
      
FROM   
    fn_dblog(NULL, NULL)   
WHERE   
    Operation = 'LOP_DELETE_ROWS'   

But the result is not clearly for us to understand,so we can use tools such as ApexSQL Log to do that.For example, I deleted one record id=1 in my TEST table and then cascading table's id also be deleted. Then use ApexSQL log to check which record be deleted, and there are undo and redo options for your to choose.

More information:recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.