SQL Query to look for nulls from two different tables while adding in a new column that states what table its from
Is there a reason you can't simply hardcode a literal of the name of the SourceTable in each select?

--Demo setup
Declare @OrdersTable table (
  OrderName VARCHAR(4),
  OrderNumber INTEGER,
  OrderStatus VARCHAR(8)
);

INSERT INTO @OrdersTable
  (OrderName, OrderNumber, OrderStatus)
VALUES
  ('xx', '1', 'Active'),
  ('xx', '2', 'Active'),
  ('bbb', '3', 'Active'),
  ('abc', '4', 'Active'),
  (NULL, '5', 'Inactive');

Declare @ProcessTable table (
  ProcessTitle VARCHAR(4),
  ProcessNumber INTEGER,
  ProcessStatus VARCHAR(8)
);

INSERT INTO @ProcessTable
  (ProcessTitle, ProcessNumber, ProcessStatus)
VALUES
  ('abc', '1', 'Active'),
  ('abc', '1', 'Active'),
  ('db', '2', 'Active'),
  ('BG', '3', 'Inactive'),
  ('BG', '3', 'Inactive'),
  (Null, '4', 'Active'),
  (Null, '4', 'Inactive');

--Solution
Select OrderName, 
       OrderNumber, 
       OrderStatus,
       'OrdersTable' as SourceTable
From @OrdersTable
where OrderName is null
UNION ALL
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus,
       'ProcessTable' as SourceTable
From @ProcessTable
where ProcessTitle is null




| OrderName | OrderNumber | OrderStatus | SourceTable  |
|-----------|-------------|-------------|--------------|
| NULL      | 4           | Active      | ProcessTable |
| NULL      | 4           | Inactive    | ProcessTable |
| NULL      | 5           | Inactive    | OrdersTable  |