USE [master]
GO

BEGIN TRY
-- must set database name, number of data files and data file size
	-- database name
	DECLARE @databaseName NVARCHAR(100) = 'some data base'

	-- number of data files
	DECLARE @numberDataFiles TINYINT = 2
	-- normally should not have LESS then 2 data files

	-- size of data files
	DECLARE @ndfSize DECIMAL(6, 3) = 2
	-- allowed sizes: 0.125, 0.25, 0.5, 1.0, 2.0, 3.0, 4.0
	

	-- set to one to enable filestream
	DECLARE @withFileStream BIT = 0
	-- allowed values 0 or 1 - default=0 (no filestream)

-- if using database switching then set hasSwitch=1
	-- uses database-switching and snapshot - default=FALSE - on TRUE will generate script for two databases (__00 & __01) plus snapshot
	DECLARE @hasSwitch BIT = 0


	-- ensure ndf file size is allowed
	IF NOT (@ndfSize IN (0.125, 0.25, 0.5, 1.0, 2.0, 3.0, 4.0))
	  BEGIN
		DECLARE @x VARCHAR(10) = CAST(@ndfSize AS VARCHAR(10))
		RAISERROR ('.ndf File Size of %s is NOT an allowed value for ndfSize - cannot continue! [allowed: 0.125, 0.25, 0.5, 1.0, 2.2, 3.0, 4.0]', 14, 2, @x);
	  END

	-- trim and remove square brackets [] from databaseName
	SET @databaseName = LTRIM(RTRIM(REPLACE(REPLACE(@databaseName, '[', ''), ']', '')))


	-- script vars
	DECLARE @s CHAR(4) = '    '		-- spacer for indents
	DECLARE @dataDir NVARCHAR(200) = ( SELECT PropertyValue FROM [master].dbo.fn_List_ServerProperties() WHERE PropertyName = 'Data_Directory' )
	DECLARE @LogDir NVARCHAR(200) = ( SELECT PropertyValue FROM [master].dbo.fn_List_ServerProperties() WHERE PropertyName = 'Log_Directory' )
	DECLARE @fsDir NVARCHAR(200) = ( SELECT PropertyValue FROM [master].dbo.fn_List_ServerProperties() WHERE PropertyName = 'Filestream_Directory' )
	DECLARE @snapshotDir NVARCHAR(200) = ( SELECT PropertyValue FROM [master].dbo.fn_List_ServerProperties() WHERE PropertyName = 'Snapshot_Directory' )
	DECLARE @gb INT = 1024
	-- file size as string
	DECLARE @nSize NVARCHAR(10) = CAST(CAST((@gb * @ndfSize) AS INT) AS NVARCHAR(10)) + 'MB'
	-- file growth based on file size: if greater than 256MB (0.25) then 512MB else 128MB
	DECLARE @fileGrowth NVARCHAR(10) = CASE WHEN (@ndfSize > 0.25) THEN '512MB' ELSE '128MB' END


	-- table to hold all sql statements
	DECLARE @tbl TABLE (Tbl_PK INT IDENTITY (1, 1) NOT NULL, SqlString NVARCHAR(MAX) NOT NULL, ForSwitch BIT DEFAULT (1))


	-- add USE MASTER to sqlTable
	INSERT INTO @tbl (SqlString) SELECT ''
	INSERT INTO @tbl (SqlString) SELECT ''
	INSERT INTO @tbl (SqlString) SELECT '/*  create database ' + @databaseName + '  */'
	INSERT INTO @tbl (SqlString) SELECT 'USE [master]'
	INSERT INTO @tbl (SqlString) SELECT 'GO'
	INSERT INTO @tbl (SqlString) SELECT ''


	-- script test for existing DB
	INSERT INTO @tbl (SqlString) SELECT 'IF EXISTS ( SELECT * FROM sys.databases WHERE name = ''' + @databaseName + ''' )'
	INSERT INTO @tbl (SqlString) SELECT '  BEGIN'
	INSERT INTO @tbl (SqlString) SELECT @s + 'RAISERROR (''ERROR: Database [%s] already exists on this server - cannot continue!'', 10, 1, ''' + @databaseName + ''')'
	INSERT INTO @tbl (SqlString) SELECT '  END'


	-- script else create database
	INSERT INTO @tbl (SqlString) SELECT 'ELSE'
	INSERT INTO @tbl (SqlString) SELECT '  BEGIN'
	INSERT INTO @tbl (SqlString) SELECT ''
	INSERT INTO @tbl (SqlString) SELECT @s + 'CREATE DATABASE [' + @databaseName + ']'
	INSERT INTO @tbl (SqlString) SELECT @s + @s + 'ON PRIMARY'
	INSERT INTO @tbl (SqlString) SELECT @s + @s + @s + '(NAME = ' + @databaseName + '__primary, FILENAME = ''' + @dataDir + '\' + @databaseName + '__primary.mdf'', SIZE = 16MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB),'
	INSERT INTO @tbl (SqlString) SELECT ''

	-- ndf files
	INSERT INTO @tbl (SqlString) SELECT @s + @s + 'FILEGROUP DATA DEFAULT'
										+ @s + ' -- sizes -- 128MB -- 256MB -- 512MB' + ' -- ' + CAST(@gb AS NVARCHAR(10)) + 'MB' + ' -- ' + CAST(@gb * 2 AS NVARCHAR(10)) + 'MB'
										+ ' -- ' + CAST(@gb * 3 AS NVARCHAR(10)) + 'MB' + ' -- ' + CAST(@gb * 4 AS NVARCHAR(10)) + 'MB'
										+ ' -- ' + CAST(@gb * 5 AS NVARCHAR(10)) + 'MB' + ' -- ' + CAST(@gb * 6 AS NVARCHAR(10)) + 'MB'
										+ ' -- ' + CAST(@gb * 7 AS NVARCHAR(10)) + 'MB' + ' -- ' + CAST(@gb * 8 AS NVARCHAR(10)) + 'MB'

	-- add data files for default filegroup "DATA"
	DECLARE @i INT = 1
	WHILE @i <= @numberDataFiles
	  BEGIN
		-- data file
		INSERT INTO @tbl (SqlString)
			SELECT @s + @s + @s + '(NAME = ' + @databaseName + '__data_' + CAST(@i AS NVARCHAR(10))
									+ ', FILENAME = ''' + @dataDir + '\' + @databaseName + '__data_' + CAST(@i AS NVARCHAR(10)) + '.ndf'','
									+ ' SIZE = ' + @nSize + ', MAXSIZE = UNLIMITED, FILEGROWTH = ' + @fileGrowth + ')'
									+ CASE WHEN (@i < @numberDataFiles) THEN ',' ELSE '' END
		-- incremetn counter i
		SET @i += 1
	  END
	INSERT INTO @tbl (SqlString) SELECT ''


	-- filestream if needed
	IF @withFileStream = 0
	  BEGIN
		INSERT INTO @tbl (SqlString) SELECT @s + @s + '-- Un-Comment if FILESTREAM is needed'
	  END
	INSERT INTO @tbl (SqlString) SELECT @s + @s + CASE WHEN @withFileStream = 0 THEN '--' ELSE '' END + ','
	INSERT INTO @tbl (SqlString) SELECT @s + @s + CASE WHEN @withFileStream = 0 THEN '--' ELSE '' END + 'FILEGROUP [FILESTREAM] CONTAINS FILESTREAM  DEFAULT'
	INSERT INTO @tbl (SqlString) SELECT @s + @s + CASE WHEN @withFileStream = 0 THEN '--' ELSE '' END + @s + '(NAME = ' + @databaseName + '__fs, FILENAME = ''' + @fsDir + '\' + @databaseName + '__fs'')'
	INSERT INTO @tbl (SqlString) SELECT ''

	-- log file
	INSERT INTO @tbl (SqlString) SELECT @s + @s + 'LOG ON'
	INSERT INTO @tbl (SqlString) SELECT @s + @s + @s + '(NAME = ' + @databaseName + '__log,'
											+ ' FILENAME = ''' + @logDir + '\' + @databaseName + '__log.ldf'','
											+ ' SIZE = ' + @nSize + ',' + ' MAXSIZE = UNLIMITED,'
											+ ' FILEGROWTH = ' + @fileGrowth + ')'
	INSERT INTO @tbl (SqlString) SELECT ''

	-- set collation to CI/AS
	INSERT INTO @tbl (SqlString) SELECT @s + @s + 'COLLATE Latin1_General_CI_AS'
	INSERT INTO @tbl (SqlString) SELECT '  END'

	-- print results
	INSERT INTO @tbl (SqlString) SELECT 'PRINT ''DB [' + @databaseName + '] created'''
	INSERT INTO @tbl (SqlString) SELECT 'GO'
	INSERT INTO @tbl (SqlString) SELECT ''

	-- set dbOwner to sa
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '-- set dbOwner on created databases', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + ']', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'EXECUTE sp_changedbowner @loginame = ''sa'', @map = false', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	IF (@hasSwitch = 1)
	  BEGIN
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + '__01]', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'EXECUTE sp_changedbowner @loginame = ''sa'', @map = false', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	  END


	-- set RecoveryModel to SIMPLE
--'ALTER DATABASE [' + Name + '] SET RECOVERY SIMPLE WITH NO_WAIT'
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '-- set RecoveryModel to SIMPLE on created databases', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + ']', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'ALTER DATABASE [' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + '] SET RECOVERY SIMPLE WITH NO_WAIT', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	IF (@hasSwitch = 1)
	  BEGIN
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + '__01]', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'ALTER DATABASE [' + @databaseName + '__01] SET RECOVERY SIMPLE WITH NO_WAIT', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	  END

-- set compatibility to SQL2017 (140)
--'ALTER DATABASE [' + Name + '] SET COMPATIBILITY_LEVEL = 140'
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '-- set compatibility to SQL2017 (140) on created databases', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + ']', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'ALTER DATABASE [' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + '] SET COMPATIBILITY_LEVEL = 140', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	IF (@hasSwitch = 1)
	  BEGIN
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [' + @databaseName + '__01]', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'ALTER DATABASE [' + @databaseName + '__01] SET COMPATIBILITY_LEVEL = 140', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	  END



	-- snapshot if needed
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '/*  script for ' + @databaseName + ' snapshot' + CASE WHEN (@hasSwitch = 1) THEN '  */' ELSE '' END, 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [master]', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'IF  EXISTS (SELECT name FROM sys.databases WHERE name = ''' + @databaseName + ''')', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '  BEGIN', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT @s + 'DROP DATABASE ' + @databaseName, 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '  END', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'CREATE DATABASE ' + @databaseName, 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT @s + 'ON', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT @s + @s + '(NAME = ' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + '__primary, FILENAME = ''' + @snapshotDir + '\' + @databaseName + '.ss''),', 0

	-- add snapshot data file for each datafile
	SET @i = 1
	WHILE @i <= @numberDataFiles
	  BEGIN
		-- data file
		INSERT INTO @tbl (SqlString, ForSwitch)
			SELECT @s + @s + '(NAME = ' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END + '__data_' + CAST(@i AS NVARCHAR(10)) + ','
					+ ' FILENAME = ''' + @snapshotDir + '\' + @databaseName + '_' + CAST(@i AS NVARCHAR(10)) + '.ss'')'
					+ CASE WHEN (@i < @numberDataFiles) THEN ',' ELSE '' END, 0
		-- incremetn counter i
		SET @i += 1
	  END

	-- finish snapshot
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT  @s + 'AS SNAPSHOT OF ' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN '__00' ELSE '' END, 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	-- print switch/snapshot results
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'PRINT ''SNAPSHOT [' + @databaseName + '] created''', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	-- end comment if not switching
	IF (@hasSwitch = 0)
	  BEGIN
		INSERT INTO @tbl (SqlString, ForSwitch) SELECT '*/', 0
	  END

	-- print results
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'USE [master]', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'GO', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '/* generated script for:', 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT 'CREATE DATABASE ' + @databaseName + CASE WHEN (@hasSwitch = 1) THEN ' __00 __01 & snapshot' ELSE '' END, 0
	INSERT INTO @tbl (SqlString, ForSwitch) SELECT '*/', 0


	-- return all SQL strings - if hasSwitch=TRUE then select forSwitch records twice (with each name) else only select once
	IF (@hasSwitch = 0)
	  BEGIN
		SELECT SqlString FROM @tbl ORDER BY Tbl_PK
	  END
	ELSE
	  BEGIN
		SELECT SqlString
		FROM
			(
				-- for db __00
				SELECT REPLACE(SqlString, @databaseName, @databaseName + '__00') AS SqlString, 1 AS SortOrder, Tbl_PK
				FROM @tbl WHERE ForSwitch = 1
				-- for db __00
			  UNION ALL
				-- for db __01
				SELECT REPLACE(SqlString, @databaseName, @databaseName + '__01') AS SqlString, 2 AS SortOrder, Tbl_PK
				FROM @tbl WHERE ForSwitch = 1
				-- for db __01
			  UNION ALL
				-- for snapshot
				SELECT SqlString, 3 AS SortOrder, Tbl_PK FROM @tbl WHERE ForSwitch = 0
				-- for snapshot
			) s
		ORDER BY SortOrder, Tbl_PK
	  END


	------ return file directories for proofing
	----SELECT @dataDir AS Data_Directory, @LogDir AS Log_Directory, @fsDir AS Filestream_Directory, @snapshotDir AS Snapshot_Directory

END TRY

BEGIN CATCH
	EXECUTE [master].dbo.sp_RethrowError
END CATCH