Delete unrelated records in one of some related tables

Question

There is a table MainTable with a column MyField. There are some tables related by foreign keys to MainTable. Their names are unknown.

I need:

• to find and delete all rows in MainTable which have no relation to any other table
• query should return all MyField values of these deleted rows

Or as an option: query should return all MyField values for rows which are to be deleted.

Thanks!

UpDate1 Sorry I forgot to say I use Sql Server 2005. MainTable has PK. Other tables have FK.

Example

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. Query should return

A

BB

CCC

Answer 1

You need dynamic SQL for this.

You can use the system views to pull out the foreign key information and build up a dynamic statement, either SELECT or 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<>fiddle