how to get any record in any table that would violate a foreign key constraint that would prevent my delete? basically I am looking for something like:

before you can delete record x from my_table you need to:

1) delete x,y and z from table1

2) delete m and n from table2

3) delete a,b,c from table3

I have not starting coding yet, I am posting the question because sometimes other DBAs have had the same idea and they might be willing to share the code.

I have though, seen similar questions (see below - but they haven't fulfilled my requirements)

Checking referential integrity rule violation before deletion

Checking rule violations before deleting a record

and this is an interesting article related to this matter:

Handling Constraint Violations and Errors in SQL Server

Please note that I don't want to disable any constraints. I just want to check all that will break if I do a delete - before I go ahead and do it.
I have a table called tblBOrder which references a bunch of different tables.

when I run the following script to check all the tables it references I get the result below.

declare @table sysname 
declare @Obj int 

select @table            = 'tblBOrder'                   --'dbo.tblBCataloguePriceSet'   --'tblBCataloguePriceSetItem'   
select @referenced_table = ''                            --'dbo.tblBCataloguePriceSet'   

select @obj     = OBJECT_ID(@table)
select @ref_obj = OBJECT_ID(@referenced_table)

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    sch.name AS [referenced_schema],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch2
    ON tab2.schema_id = sch2.schema_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where 1=1
  --AND ( @ref_obj IS NULL OR @ref_obj = tab2.object_id )
  AND ( @OBJ IS NULL OR @obj = tab1.object_id)




Now I want to delete a specific record from my order table.
this is the record I want to delete: USZ000026936

Using the following script I generate a script to view all the records that I need to get rid of first, before I can delete my USZ000026936 in the order table.

SET NOCOUNT ON

declare @RowId varchar(100)
declare @TableName sysname 
declare @Command varchar(max) 


select @RowID = 'USZ000026936'
select @TableName = 'tblBOrder'


select fkc.*
from sys.foreign_key_columns fkc
    inner join sys.columns col 
           on fkc.parent_object_id = col.object_id 
          and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName




select @Command = isnull(@Command + CHAR(10) + CHAR(13) + ' union all ', '') + 'select ''' + object_name(parent_object_id) + 
    ''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + '''' + cast(@RowId as varchar) + '''' + ')' 
from sys.foreign_key_columns fkc
    join sys.columns col on
        fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

print @command


and here is the script generated:

select 'tblBOrderItem' where exists(select * from tblBOrderItem where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRefund2' where exists(select * from tblBOrderRefund2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderAuth2' where exists(select * from tblBOrderAuth2 where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderHistory' where exists(select * from tblBOrderHistory where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderPayment' where exists(select * from tblBOrderPayment where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderOfferOverride' where exists(select * from tblBOrderOfferOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderShipOverride' where exists(select * from tblBOrderShipOverride where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderSpecialDeliveryInstruction' where exists(select * from tblBOrderSpecialDeliveryInstruction where strBxOrderNo = 'USZ000026936')

 union all select 'tblBReturn' where exists(select * from tblBReturn where strBxOrderNo = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo1 = 'USZ000026936')

 union all select 'tblBOrderRelationship' where exists(select * from tblBOrderRelationship where strBxOrderNo2 = 'USZ000026936')

 union all select 'tblPanViewerAuditLog' where exists(select * from tblPanViewerAuditLog where strBxOrderNo = 'USZ000026936')