SELECT * 
FROM TABLEA 
OUTER UNION CORR
SELECT * 
FROM TABLEB
OUTER UNION CORR
SELECT * 
FROM TABLEC

SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15 
FROM TABLEA AS X
UNION ALL
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15 
FROM TABLEB AS X
OUTER UNION CORR
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15 
FROM TABLEC AS X

-- SETUP
CREATE TABLE TableA (
    id int PRIMARY KEY CLUSTERED,
    [1] int,
    [2] int,
    [3] int,
    [4] int,
    [5] int,
    [6] int,
    [7] int,
    [8] int,
    [9] int,
    [10] int
)



CREATE TABLE TableB (
    id int PRIMARY KEY CLUSTERED,
    [1] int,
    [3] int,
    [4] int,
    [5] int,
    [8] int,
    [9] int,
    [10] int,
    [11] int,
    [12] int
)


CREATE TABLE TableC (
    id int PRIMARY KEY CLUSTERED,
    [1] int,
    [2] int,
    [3] int,
    [5] int,
    [6] int,
    [7] int,
    [9] int,
    [11] int,
    [12] int,
    [13] int
)

ALTER PROCEDURE unionTables
AS
BEGIN
    
    SET NOCOUNT ON;
    
    DECLARE @sql nvarchar(max)
    
    SELECT @sql = STUFF((
        SELECT ',' + QUOTENAME(name) AS [text()]
        FROM sys.columns
        WHERE object_id IN (
            OBJECT_ID('TableA'),
            OBJECT_ID('TableB'),
            OBJECT_ID('TableC')
        )
        GROUP BY name 
        HAVING COUNT(*) = 3
        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 (
            OBJECT_ID('TableA'),
            OBJECT_ID('TableB'),
            OBJECT_ID('TableC')
        )
        FOR XML PATH('')
    ),1,10,SPACE(0))
    
    EXEC(@sql)
END

SELECT     'Beach' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments, 
                      ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM         BeachInspection
UNION ALL
SELECT     'BodyArt' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments, 
                      ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM         BodyArtInspection
.......
UNION ALL
SELECT     'Water' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments, 
                      ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM         WaterInspection

SELECT     'Beach' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM         BeachViolation
UNION ALL
......
SELECT     'Summer Camp' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM         SummerCampViolation
UNION ALL
SELECT     'Water' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM         WaterViolation

SELECT     'BeachFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID, 
                      WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID, 
                      Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate, 
                      PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue, 
                      NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding, 
                      PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty, 
                      PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName, 
                      MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress, 
                      PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea, 
                      PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3, 
                      DateModifiedInThisFile
FROM         BeachFacility
UNION ALL
.....
SELECT     'WaterFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID, 
                      WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID, 
                      Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate, 
                      PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue, 
                      NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding, 
                      PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty, 
                      PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName, 
                      MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress, 
                      PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea, 
                      PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3, 
                      DateModifiedInThisFile
FROM         WaterFacility

... object_id IN (
    OBJECT_ID('TableA'),
    OBJECT_ID('TableB'),
    OBJECT_ID('TableC'),
    OBJECT_ID('TableD'),
    OBJECT_ID('TableE')
)

SELECT     FacilitiesList.Name
FROM         FacilitiesList INNER JOIN
                      InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN
                      ViolationList ON InspectionsList.UNID = ViolationList.UNID

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

ALTER PROCEDURE refresh_view_for_inspection_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('BeachInspection')),
(OBJECT_ID('BodyArtInspection')),
(OBJECT_ID('CampInspection')),
(OBJECT_ID('ChildCareInspection')),
(OBJECT_ID('GeneralInspection')),
(OBJECT_ID('HotelInspection')),
(OBJECT_ID('LaborCampInspection')),
(OBJECT_ID('MobileHomeInspection')),
(OBJECT_ID('PoolInspection')),
(OBJECT_ID('SchoolInspection')),
(OBJECT_ID('SepticRemovalInspection')),
(OBJECT_ID('SummerCampInspection')),
(OBJECT_ID('WaterInspection'));
    
    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(''InspectionListAutoTest'',''V'') IS NOT NULL DROP VIEW InspectionListAutoTest;');
    
    SET @sql = 'CREATE VIEW InspectionListAutoTest AS ' + @sql;
    
    EXEC(@sql);
END

Running [dbo].[refresh_view_for_unioned_tables].

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[refresh_view_for_unioned_tables]