I agree with rVadim that you should probably consider another approach if possible.

Having said this, here an example code for a procedure to drop and create the view:

CREATE PROCEDURE dbo.USP_REFRES_THE_LATEST_VIEW

AS

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'THE_LATEST_VIEW'

AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'VIEW') DROP VIEW dbo.THE_LATEST_VIEW;

DECLARE @SQL_STR NVARCHAR(MAX) = N'';

SELECT @SQL_STR = STUFF(

(

SELECT NCHAR(13) + NCHAR(10) + 'UNION ALL ' + N'SELECT * FROM ' + T.TABLE_SCHEMA + N'.TBL_MYSTUFF' AS [text()]

FROM INFORMATION_SCHEMA.TABLES T

WHERE T.TABLE_NAME = N'TBL_MYSTUFF'

FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),1,12,'');

SELECT @SQL_STR = N'CREATE VIEW dbo.THE_LATEST_VIEW AS ' + @SQL_STR;

EXEC sp_executesql @SQL_STR;