CREATE TABLE dbo.InconvenientTable
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,

    CONSTRAINT PK_InconvenientTable
        PRIMARY KEY (Id)
);
GO

CREATE TABLE dbo.MainTable1
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable1_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

CREATE TABLE dbo.MainTable2
(
    Id int IDENTITY(1,1) NOT NULL,
    [Description] varchar(10) NOT NULL,
    InconvenientTableId int NOT NULL,

    CONSTRAINT FK_MainTable2_InconvenientTable
        FOREIGN KEY (InconvenientTableId)
        REFERENCES dbo.InconvenientTable (Id)
);

INSERT INTO dbo.InconvenientTable
    ([Description])
VALUES
    ('One'),
    ('Two'),
    ('Three');
GO

INSERT INTO dbo.MainTable1
    ([Description], InconvenientTableId)
VALUES
    ('One', 1),
    ('Two', 2),
    ('Three', 3);

INSERT INTO dbo.MainTable2
    ([Description], InconvenientTableId)
VALUES
    ('Two', 2);

CREATE VIEW dbo.InconvenientTable_RowsToDelete
AS
SELECT it.*
FROM dbo.InconvenientTable it
WHERE
    NOT EXISTS (SELECT NULL FROM dbo.MainTable1 mt1 WHERE mt1.InconvenientTableId = it.Id)
    AND NOT EXISTS (SELECT NULL FROM dbo.MainTable2 mt2 WHERE mt2.InconvenientTableId = it.Id);
GO

DELETE dbo.MainTable2 WHERE InconvenientTableId = 2;
DELETE dbo.MainTable1 WHERE InconvenientTableId = 2;