删除相关表中不相关的记录

Question

有一个表MainTable，它有一个列MyField。有一些表通过外键与MainTable相关联。他们的名字是未知的。

我需要:

• 查找并删除MainTable中与其他表无关的所有行
• 查询应该返回这些删除行的所有MyField值

或者作为选项:查询应该返回要删除的行的所有MyField值。

谢谢!

UpDate1对不起，我忘了说我用的是Sql Server 2005。MainTable有PK。

create table MainTable
(
ID int IDENTITY primary key,
MyField nvarchar(256)
);
create table OtherTable1
(
ID int IDENTITY primary key,
MainTableID int foreign key references MainTable(ID)
);
create table OtherTable2
(
ID int IDENTITY primary key,
MainTableID int foreign key references MainTable(ID)
);
-- and so on
insert into MainTable (MyField) values('A');
insert into MainTable (MyField) values('BB');
insert into MainTable (MyField) values('CCC');
insert into MainTable (MyField) values('DDDD');
insert into MainTable (MyField) values('FFFFF');
insert into OtherTable1 (MainTableID) values(1);
insert into OtherTable1 (MainTableID) values(2);
insert into OtherTable2 (MainTableID) values(3);

Result i need:

1. MainTable

ID MyField

4 DDDD

5 FFFFF

2. 查询应该返回

BBCCC

Answer 1

您需要动态SQL。

您可以使用系统视图来提取外键信息并构建一个动态语句，SELECT或DELETE。

DECLARE @sql nvarchar(max) = '
SELECT t.MyField
-- alternately DELETE t
FROM MainTable t
WHERE ' + (
SELECT STRING_AGG(CAST(
'
NOT EXISTS (SELECT 1
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' t2.
WHERE ' + c.ForeignKeys + ')
'
AS nvarchar(max)), '
AND '
)
FROM sys.tables MyTable
JOIN sys.foreign_keys fk ON fk.referenced_object_id = MyTable.object_id
JOIN sys.tables t ON t.object_id = fk.parent_object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
OUTER APPLY (
SELECT STRING_AGG('t2.' + QUOTENAME(c.name) + ' = t.' + QUOTENAME(cMyTable.name), ' AND ')
FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
JOIN sys.columns cMyTable ON cMyTable.object_id = MyTable.object_id AND cMyTable.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
) c(ForeignKeys)
WHERE MyTable.name = 'MainTable'
);
PRINT @sql;  -- for testing
EXEC sp_executesql @sql;

,db&lt的在小提琴