1

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.

  • 2
    Why not just try? Violations will be reported and it will not delete. – paparazzo Sep 18 '16 at 15:18
  • What's the use case for this? – Martin Smith Sep 18 '16 at 17:27
  • 1
    Aaron Bertrand has a script to drop and recreate foreign keys. Does this get you started? mssqltips.com/sqlservertip/3347/… – Erik Darling Sep 18 '16 at 18:21
0

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)

enter image description here

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.