USE [master]
GO
IF OBJECT_ID('dbo.sp_DBScript') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_DBScript AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_DBScript
@NewDatabase VARCHAR(256) = NULL,
@PostFix VARCHAR(256) = NULL,
@DataFileMB INT = 1000,
@LogFileMB INT = 100,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@DropExisting BIT = 1,
@PrintSQL BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'RETURN'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_DBScript',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO
'
SELECT @NewDatabase = ISNULL(@NewDatabase, DB_NAME()),
@PostFix = ISNULL(@PostFix, '_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(9), GETDATE(), 114), ':', ''))
SELECT @SQL = CASE WHEN @DropExisting = 1 THEN '/* --------- DROP EXISTING ---------
USE master
GO
DECLARE @DBNAME VARCHAR(256) = ''' + @NewDatabase + ''',
@SQL VARCHAR(MAX),
@PrintSQL BIT = 1,
@ExecSQL BIT = 1
SELECT @SQL = Util.dbo.StringConcat(''KILL '' + CAST(SPID AS VARCHAR), ''
'')
FROM sys.sysprocesses (NOLOCK)
WHERE dbid = DB_ID(@DBNAME)
IF @PrintSQL = 1
PRINT @SQL
IF @ExecSQL = 1
AND @sql <> '''' EXEC(@SQL )
GO
DROP DATABASE [' + @NewDatabase + ']
--------- DROP EXISTING --------- */
GO
' ELSE ''
END + REPLACE(REPLACE(SUBSTRING(DbCreateSQL, 1, LEN(DbCreateSQL) - 1), ',FILEGROUP', ',
FILEGROUP'), ',LOG ON', '
LOG ON') + '
GO
-- EXEC sp_db_increased_partitions @dbname = ''' + @NewDatabase + ''', @increased_partitions = ''ON''
GO
-- USE [' + @NewDatabase + '] EXEC sp_cdc_enable_db;
GO
'
FROM (SELECT 'CREATE DATABASE [' + @NewDatabase + ']
' + Util.dbo.StringConcat(SQL, '') AS DbCreateSQL
FROM (SELECT TOP 999999
FileGroup + '
' + Util.dbo.StringConcat('( NAME = N''' + a.name + ''', FILENAME = N''' + REPLACE(a.FileName, JustFile, @NewDatabase + '_' + a.name + @PostFix + Extension)
+ ''', SIZE = ' + ISNULL(CAST(CASE WHEN Logfile = 1 THEN @LogFileMB
ELSE @DataFileMB
END AS VARCHAR), CAST(CAST(a.SIZE AS BIGINT) * 8 / 1024 AS VARCHAR)) + 'MB, MAXSIZE = ' + MaxSizeStr
+ ', FILEGROWTH = ' + GrowthStr + '),', '
') AS SQL
FROM sys.sysfiles a (NOLOCK)
LEFT OUTER JOIN sys.filegroups b (NOLOCK) ON a.groupid = b.data_space_id
CROSS APPLY (SELECT CASE WHEN status & 2 = 2 THEN 1
ELSE 0
END AS Diskfile,
CASE WHEN status & 64 = 64 THEN 1
ELSE 0
END AS Logfile,
CASE WHEN status & 1048576 = 1048576 THEN CAST (a.growth AS VARCHAR) + '%'
ELSE CAST(CAST(a.growth AS BIGINT) * 8 / 1024 AS VARCHAR) + 'MB'
END AS GrowthStr,
CASE WHEN maxsize = -1 THEN 'UNLIMITED'
ELSE CAST(CAST(maxsize AS BIGINT) * 8 / 1024 AS VARCHAR) + 'MB'
END AS MaxSizeStr) cs
CROSS APPLY (SELECT CASE WHEN a.groupid = 1 THEN 0
WHEN Logfile = 1 THEN 2
ELSE 1
END AS GroupType,
CASE WHEN a.groupid = 1 THEN 'ON PRIMARY'
WHEN Logfile = 1 THEN 'LOG ON'
WHEN Diskfile = 1 THEN 'FILEGROUP [' + b.name + ']' + CASE WHEN is_default = 1 THEN ' DEFAULT'
ELSE ''
END
ELSE 'N/A'
END AS FileGroup,
CASE WHEN a.groupid = 1 THEN '.mdf'
WHEN Logfile = 1 THEN '.ldf'
WHEN Diskfile = 1 THEN '.ndf'
ELSE '.n/a'
END AS Extension) fg
CROSS APPLY (SELECT TOP 1
field AS JustFile
FROM Util.dbo.ParseDelimited(a.FileName, '\')
ORDER BY fieldnum DESC) j
GROUP BY GroupType,
FileGroup
ORDER BY GroupType) k) k
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_DBScript
GO