The procedure builds a dynamic SQL statement that UNIONs all the tables selecting all the columns that are present in all the tables.

You can see the statement being run adding "PRINT @sql" right before "EXEC(@sql)".

The statement runs in the moment you call it and does not affect the tables.

If you prefer having a view, you could modify the procedure to update the view definition and invoke it when the tables change.

CREATE PROCEDURE refresh_view_for_unioned_tables

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

DECLARE @tables TABLE (

table_id int PRIMARY KEY CLUSTERED

);

INSERT INTO @tables

VALUES (OBJECT_ID('TableA')),

(OBJECT_ID('TableB')),

(OBJECT_ID('TableC')),

(OBJECT_ID('TableD')),

(OBJECT_ID('TableE'));

SELECT @sql = STUFF((

SELECT ',' + QUOTENAME(name) AS [text()]

FROM sys.columns

WHERE object_id IN (

SELECT table_id

FROM @tables

)

GROUP BY name

HAVING COUNT(*) = (SELECT COUNT(*) FROM @tables)

FOR XML PATH('')

),1,1,SPACE(0));

SELECT @sql = STUFF((

SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]

FROM sys.tables

WHERE object_id IN (

SELECT table_id

FROM @tables

)

FOR XML PATH('')

),1,10,SPACE(0));

EXEC('IF OBJECT_ID(''unioned_tables'',''V'') IS NOT NULL DROP VIEW unioned_tables;');

SET @sql = 'CREATE VIEW unioned_tables AS ' + @sql;

EXEC(@sql);

END