ALTER PROCEDURE [dbo].[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('FoodInspection')),
(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 
        
        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