从SQL 2008中的外键关系生成删除语句？ [英] Generate Delete Statement From Foreign Key Relationships in SQL 2008?

问题描述

是否可以通过脚本/工具来生成基于表fk关系的删除语句。

我有表：DelMe（ID），有30个表的fk引用它的ID，我需要先删除，是否有一些工具/脚本，我可以运行，将生成基于FK关系的30删除语句（btw我知道关于级联删除关系，我不能在这个现有的数据库中使用它）

$ b b

我正在使用Microsoft SQL Server 2008

解决方案

DELETE触发器：
（此变体仅支持单列FK）

$ p $选择'删除'+ detail.name +'WHERE '+ dcolumn.name +'= @'+ mcolumn.name AS stmt，
'DELETE'+ detail.name +'FROM'+ detail.name +'INNER JOIN deleted ON'+
detail.name +'。'+ dcolumn.name +'= deleted。'+ mcolumn.name AS trg
FROM sys.columns AS mcolumn
INNER JOIN sys.foreign_key_columns ON mcolumn.object_id =
sys.foreign_key_columns.referenced_object_id
AND mcolumn.column_id = sys.foreign_key_columns.referenced_column_id
INNER JOIN sys.tables AS master ON mcolumn.object_id = master.object_id
INNER JOIN sys.columns AS dcolumn
ON sys.foreign_key_columns.parent_object_id = dcolumn.object_id
AND sys.foreign_key_columns.parent_column_id = dcolumn.column_id
INNER JOIN sys.tables详细信息ON dcolumn.object_id = detail.object_id
WHERE（master.name = N'MyTableName'）

Is it possible via script/tool to generate a delete statement based on the tables fk relations.

i.e. I have the table: DelMe(ID) and there are 30 tables with fk references to its ID that I need to delete first, is there some tool/script that I can run that will generate the 30 delete statements based on the FK relations for me ?

(btw I know about cascade delete on the relations, I can't use it in this existing db)

I'm using Microsoft SQL Server 2008

解决方案

DELETE statements generated for use in SP with parameter, and as ON DELETE triggers: (this variant supports single column FKs only)

SELECT 'DELETE '+detail.name+' WHERE '+dcolumn.name+' = @'+mcolumn.name AS stmt, 
    'DELETE ' + detail.name + ' FROM ' + detail.name + ' INNER JOIN deleted ON ' + 
    detail.name + '.' + dcolumn.name + ' = deleted.' + mcolumn.name AS trg
FROM sys.columns AS mcolumn 
INNER JOIN sys.foreign_key_columns ON mcolumn.object_id = 
            sys.foreign_key_columns.referenced_object_id 
    AND  mcolumn.column_id = sys.foreign_key_columns.referenced_column_id 
INNER JOIN sys.tables AS master ON mcolumn.object_id = master.object_id 
INNER JOIN sys.columns AS dcolumn 
    ON sys.foreign_key_columns.parent_object_id = dcolumn.object_id 
    AND sys.foreign_key_columns.parent_column_id = dcolumn.column_id 
INNER JOIN sys.tables AS detail ON dcolumn.object_id = detail.object_id
WHERE (master.name = N'MyTableName')

这篇关于从SQL 2008中的外键关系生成删除语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！