Skip to content

Instantly share code, notes, and snippets.

@LitKnd
Last active March 13, 2020 14:23
Show Gist options
  • Save LitKnd/6d419652afbc33d3a2596986604eb7a1 to your computer and use it in GitHub Desktop.
Save LitKnd/6d419652afbc33d3a2596986604eb7a1 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment