-- A comma-separated list of fully-qualified table names that should be marked for recompile
DECLARE @ObjectRecompileList VARCHAR(MAX) = 'dbo.Table1,dbo.Table2,dbo.Proc1';

DECLARE @ObjectsToRecompile TABLE
(
    ObjectId     INT,
    ObjectSchema VARCHAR(1000),
    ObjectName   VARCHAR(1000)
)
DECLARE @ObjectsToVerify TABLE
(
    ObjectId     INT,
    ObjectType   VARCHAR(128),
    ObjectSchema VARCHAR(128),
    ObjectName   VARCHAR(128)
)

-- Generate list of objects to recompile
INSERT @ObjectsToRecompile (ObjectId, ObjectSchema, ObjectName)
SELECT
    o.object_id,
    s.name,
    o.name
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN STRING_SPLIT(@ObjectRecompileList, ',') r ON o.object_id = OBJECT_ID(r.value)

-- Generate list of all objects to verify after recompile
INSERT INTO @ObjectsToVerify(ObjectId, ObjectType, ObjectSchema, ObjectName)
SELECT
    o.object_id,
    CASE
        WHEN o.Type = 'FN' THEN 'Scalar Function'
        WHEN o.Type = 'IF' THEN 'Inline Table-Valued Function'
        WHEN o.Type = 'P'  THEN 'Stored Procedure'
        WHEN o.Type = 'TF' THEN 'Table-Valued Function'
        WHEN o.Type = 'TR' THEN 'DML Trigger'
        WHEN o.Type = 'V'  THEN 'View'
    END,
    s.name,
    o.name
FROM sys.objects o
    JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
    o.Type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
    AND s.name NOT IN ('temp')
ORDER BY s.name, o.name

-- Loop through to mark recompilation
DECLARE @LastObjectId INT;
SELECT @LastObjectId = MAX(ObjectId) FROM @ObjectsToRecompile;
WHILE @LastObjectId > 0
BEGIN

    DECLARE @sql NVARCHAR(1000);
    SELECT @sql = 'EXEC sp_recompile ''[' + ObjectSchema + '].[' + ObjectName + ']''' FROM @ObjectsToRecompile WHERE ObjectId = @LastObjectId;
    EXEC (@sql)

    SELECT @LastObjectId = ISNULL(MAX(ObjectId),0) FROM @ObjectsToRecompile WHERE ObjectId < @LastObjectId;

END

-- Since sp_recompile just marks for recompile *on next proc/function run*, force recompile immediately and log errors
SELECT o.ObjectType, o.ObjectSchema, o.ObjectName, e.error_message
FROM @ObjectsToVerify o
CROSS APPLY (SELECT error_message FROM sys.dm_exec_describe_first_result_set_for_object (o.ObjectId, NULL) WHERE error_state = 1 AND error_message LIKE 'Invalid column%') e
GROUP BY o.ObjectType, o.ObjectSchema, o.ObjectName, e.error_message
ORDER BY o.ObjectType, o.ObjectSchema, o.ObjectName