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

问题描述

是否可以通过脚本/工具根据表 fk 关系生成删除语句.

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

即我有表:DelMe(ID) 并且有 30 个表，我需要先删除其 ID 的 fk 引用，是否有一些我可以运行的工具/脚本将根据 FK 关系生成 30 个删除语句我?

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)

我使用的是 Microsoft SQL Server 2008

I'm using Microsoft SQL Server 2008

推荐答案

生成的DELETE语句用于带参数的SP中，并作为ON DELETE触发器:(此变体仅支持单列 FK)

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屋！