Another option would be a DDL trigger, which can capture index operations.
CREATE TRIGGER [IndexEventAudit]
FOR ALTER_INDEX, CREATE_INDEX, DROP_INDEX
SET NOCOUNT ON;
DECLARE @e XML = EVENTDATA();
DECLARE @sql NVARCHAR(MAX)
= @e.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(MAX)');
DECLARE @obj SYSNAME
= @e.value('(/EVENT_INSTANCE/TargetObjectName)', 'nvarchar(128)')
IF @obj = N'Primary_Table'
SET @sql = REPLACE(@sql, N'Primary_Table', N'Stage_Table');
EXEC sp_executesql @sql;
There is a narrow failure case here; if you have an index on a column that has the table name embedded. E.g. this would fail:
CREATE INDEX foo ON dbo.Primary_Table(Primary_Table_id);
Because the replaced SQL would look like this:
CREATE INDEX foo ON dbo.Stage_Table(Stage_Table_id);
-------- wrong column name ---------^^^^^^^^^^^^^^
You may also want to add preventative measures to make sure other people don't try to change the indexes on
stage_table directly, which can obviously mess with this approach (and other approaches, likely, too). Also note that you may need to extend this to capture things like constraints, and also that if there are schema changes to the primary table that aren't reflected in the secondary table, or if you have naming conflicts etc., you'll need to handle those somehow too. For example, someone could add a column to the primary table, then index it, and the index on the secondary would fail because the column change wasn't reflected there.