create n schemas with i tables each.sql

USE master;
GO


IF DB_ID('SchemaDeployDev') IS NOT NULL
BEGIN
    ALTER DATABASE SchemaDeployDev SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SchemaDeployDev;
END;

CREATE DATABASE SchemaDeployDev;
GO


USE SchemaDeployDev;
GO

DECLARE @n INT = 1,
        @i INT = 1,
        @j INT = 1,
		@schemanum int = 10,
        @schema sysname,
        @dsql NVARCHAR(MAX);

WHILE @j <= @schemanum
BEGIN
    SET @schema = N'schema' + CAST(@j AS NVARCHAR(2));
    SET @dsql = N'CREATE SCHEMA ' + @schema + N' authorization dbo;';
    --PRINT @dsql;

    EXEC sp_executesql @dsql;

    WHILE @n < 101
    BEGIN
        SET @dsql = N'CREATE TABLE ' + @schema + N'.Table' + CAST(@n AS NVARCHAR) + N' (';

        WHILE @i <= @n
        BEGIN
            SET @dsql = @dsql + N' Col' + CAST(@i AS NVARCHAR) + N' INT NULL,';

            SET @i = @i + 1;
        END;

        SET @dsql = LEFT(@dsql, LEN(@dsql) - 1) + N');';

        --PRINT @dsql;
        EXEC sp_executesql @dsql;

        SET @i = 1;
        SET @n = @n + 1;

    END;

    SET @j = @j + 1;
    SET @n = 1;

END;


SET @n = 1;
SET @j = 1;

WHILE @j < @schemanum
BEGIN

    SET @dsql = N'CREATE VIEW dbo.view' + CAST(@j AS NVARCHAR(2)) + N' AS ( SELECT * FROM schema1.Table100 ';

    WHILE @n < @schemanum
    BEGIN

        SET @dsql = @dsql + N' UNION SELECT * FROM schema' + CAST(@n AS NVARCHAR) + N'.Table100';
        SET @n = @n + 1;

    END;

    SET @n = 1;

    SET @dsql = @dsql + N' ) ';

    PRINT @dsql;
    EXEC sp_executesql @dsql;

    SET @j = @j + 1;

END;

GO