usp_PartitionDatabase

CREATE PROC usp_PartitionDatabase @dbname VARCHAR(50) = NULL, @type NCHAR(5) = NULL, @dateornumeric NVARCHAR(8) = NULL, @interval NVARCHAR(50) = NULL, @setmax NVARCHAR(10) = NULL
AS
--
-- usp_PartitionDatabase.sql - Database Partitioning statement creation.
--
-- For <interval> the options are:
-- If datetime key, interval may be: month, quarter, semester, year, between single quotes
-- If datetime key, set_max_value is a maximum date in dd-mm-yyyy format between single quotes
-- If numeric key, interval may be: hundreds, thousands, t_thousands (tens of thousand), h_thousands (hundreds of thousand), millions, between single quotes
-- If numeric key, set_max_value is a maximum number, between single quotes
--
-- EXEC usp_PartitionDatabase '<dbname>', '<right_or_left>', '<date_or_numeric>', '<interval>', '<set_max_value>'
-- 
-- 2008-12-09 Pedro Lopes (NovaBase) pedro.lopes@novabase.pt
--
-- Example with numeric key:
-- EXEC usp_PartitionDatabase 'database', 'left', 'numeric', 'h_thousands', '10000000'
--
-- Example with datetime key:
-- EXEC usp_PartitionDatabase 'database', 'right', 'date', 'quarter', '01-01-2011'
--
SET NOCOUNT ON;
SET DATEFORMAT DMY;

IF (@dbname IS NULL OR @dateornumeric IS NULL OR @interval IS NULL OR @type IS NULL) AND @interval NOT IN ('left', 'right') AND @dateornumeric NOT IN ('date', 'numeric')
	BEGIN
		SELECT 'Usage: EXEC usp_PartitionDatabase ''<dbname>'', ''<right_or_left>'', ''<date_or_numeric>'', ''<interval>'', ''<set_max_value>''' AS 'WARNING - SYNTAX ERROR!'
		RETURN
	END
	
--Common setup
DECLARE @sqlcmdfg NVARCHAR(100), @sqlcmdfile NVARCHAR(1000), @filepath NVARCHAR(500), @filepathcmd NVARCHAR(150)
DECLARE @PFunc NVARCHAR(max), @SFunc NVARCHAR(max), @setmaxdate DATETIME, @setmaxnum bigint, @i int
IF LOWER(@dateornumeric) = 'numeric'
BEGIN
	SELECT @setmaxnum = CONVERT(bigint, @setmax)
END
IF LOWER(@dateornumeric) = 'date'
BEGIN
	SELECT @setmaxdate = CONVERT(DATETIME, @setmax)
END

DECLARE @tblpath TABLE (datapath NVARCHAR(500))
CREATE TABLE ##tblfiles (FG int, FN int)
DECLARE @exfg int, @exfn int, @excmd NVARCHAR(500)
SET @interval = RTRIM(UPPER(@interval))
SET @type = RTRIM(@type)
SET @excmd = 'INSERT INTO ##tblfiles SELECT (SELECT COUNT(DISTINCT name) FROM ' + @dbname + '.sys.filegroups), (SELECT COUNT(DISTINCT name) FROM ' + @dbname + '.sys.sysfiles)'
EXECUTE sp_executesql @excmd
SET @exfg = (SELECT FG FROM ##tblfiles)
SET @exfn = (SELECT FN FROM ##tblfiles)

SET @filepathcmd = 'SELECT TOP 1 LEFT(filename, LEN(filename)-PATINDEX(''%\%'', REVERSE(filename))) FROM ' + @dbname + '.sys.sysfiles WHERE [groupid] = 1'
INSERT INTO @tblpath
EXEC sp_executesql @filepathcmd
SET @filepath = (SELECT datapath FROM @tblpath)

IF LOWER(@dateornumeric) = 'date'
BEGIN
	--Setup
	DECLARE @NewFGDate NCHAR(8), @ddiff int, @NewPFDate NCHAR(8), @NewPSDate NCHAR(8)
	DECLARE @tbldates TABLE (mdate CHAR(8), pfdate CHAR(8))
	IF LOWER(@interval) = 'month'
	BEGIN
		SELECT @ddiff = DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0), @setmaxdate)
		INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),105),'-',''),
			REPLACE(CONVERT(VARCHAR(10),(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),126),'-',''))
		SET @i = 1
		WHILE @i <> @ddiff
		BEGIN
			INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(mm, DATEDIFF(mm, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),105),'-',''),
				REPLACE(CONVERT(VARCHAR(10),(DATEADD(mm, DATEDIFF(mm, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),126),'-',''))
			SET @i = @i + 1
		END
	END
	IF LOWER(@interval) = 'quarter'
	BEGIN
		SELECT @ddiff = DATEDIFF(qq, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0), @setmaxdate)
		INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),105),'-',''),
			REPLACE(CONVERT(VARCHAR(10),(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),126),'-',''))
		SET @i = 1
		WHILE @i <> @ddiff
		BEGIN
			INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(qq, DATEDIFF(qq, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),105),'-',''),
				REPLACE(CONVERT(VARCHAR(10),(DATEADD(qq, DATEDIFF(qq, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),126),'-',''))
			SET @i = @i + 1
		END
	END
	IF LOWER(@interval) = 'semester'
	BEGIN
		SELECT @ddiff = (DATEDIFF(qq, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0), @setmaxdate))/2
		INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),105),'-',''),
			REPLACE(CONVERT(VARCHAR(10),(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),126),'-',''))
		SET @i = 1
		WHILE @i <> @ddiff
		BEGIN
			INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(qq, DATEDIFF(qq, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i*2, 0)),105),'-',''),
				REPLACE(CONVERT(VARCHAR(10),(DATEADD(qq, DATEDIFF(qq, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i*2, 0)),126),'-',''))
			SET @i = @i + 1
		END
	END
	IF LOWER(@interval) = 'year'
	BEGIN
		SELECT @ddiff = DATEDIFF(yy, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0), @setmaxdate)
		INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),105),'-',''),
			REPLACE(CONVERT(VARCHAR(10),(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)),126),'-',''))
		IF @ddiff > 1
		BEGIN
			SET @i = 1
			WHILE @i <> @ddiff
			BEGIN
				INSERT INTO @tbldates VALUES (REPLACE(CONVERT(VARCHAR,(DATEADD(yy, DATEDIFF(yy, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),105),'-',''),
				REPLACE(CONVERT(VARCHAR(10),(DATEADD(yy, DATEDIFF(yy, 0, (DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) + @i, 0)),126),'-',''))
				SET @i = @i + 1
			END
		END
	END
	--Filegroups and Files
	IF (SELECT COUNT(mdate) + @exfg FROM @tbldates) > 32767 
	BEGIN
		SELECT 'Maximum number of filegroups per database may only be 32767. Please change the input parameters...' AS 'WARNING - FATAL ERROR!'
		RETURN		
	END
	IF (SELECT COUNT(mdate) + @exfn FROM @tbldates) > 32767 
	BEGIN
		SELECT 'Maximum number of files per database may only be 32767. Please change the input parameters...' AS 'WARNING - FATAL ERROR!'
		RETURN		
	END
	DECLARE cdate CURSOR FOR SELECT mdate FROM @tbldates
	OPEN cdate
	FETCH NEXT FROM cdate INTO @NewFGDate
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sqlcmdfg = 'ALTER DATABASE [' + @dbname + '] ADD FILEGROUP FG' + @NewFGDate + ';'
		PRINT @sqlcmdfg
		SET @sqlcmdfile = 'ALTER DATABASE [' + @dbname + '] ADD FILE (NAME = ' + @dbname + '_Data_' + @NewFGDate + ', FILENAME = ''' + @filepath + '\' + @dbname + '_Data_' + @NewFGDate + '.NDF'', SIZE = 100MB, FILEGROWTH = 250MB) TO FILEGROUP FG' + @NewFGDate + ';'
		PRINT @sqlcmdfile
		FETCH NEXT FROM cdate INTO @NewFGDate
	END
	CLOSE cdate
	DEALLOCATE cdate;

	--Partition Function and Partition Scheme
	SET @PFunc = 'IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = ''pf' + LOWER(@interval) + ''')
DROP PARTITION FUNCTION [pf' + LOWER(@interval) + '];
CREATE PARTITION FUNCTION [pf' + LOWER(@interval) + '] (datetime) AS RANGE ' + @type + ' FOR VALUES ('
	SET @SFunc = 'IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = ''ps' + LOWER(@interval) + 'Range'')
DROP PARTITION SCHEME ps' + LOWER(@interval) + 'Range;
CREATE PARTITION SCHEME ps' + LOWER(@interval) + 'Range AS PARTITION pf' + LOWER(@interval) + ' TO ([PRIMARY],'
	DECLARE cdate CURSOR FOR SELECT mdate, pfdate FROM @tbldates
	OPEN cdate
	FETCH NEXT FROM cdate INTO @NewPSDate, @NewPFDate
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @PFunc = @PFunc + CHAR(39) + @NewPFDate + CHAR(39) + ','
		SET @SFunc = @SFunc + CHAR(91) + 'FG' + @NewPSDate + CHAR(93) + ','
		FETCH NEXT FROM cdate INTO @NewPSDate, @NewPFDate
	END
	CLOSE cdate
	DEALLOCATE cdate;
	SET @PFunc = LEFT(@PFunc, LEN(@PFunc)-1) + ');'
	SET @SFunc = LEFT(@SFunc, LEN(@SFunc)-1) + ');'
	PRINT @PFunc
	PRINT @SFunc

	PRINT '-- Table creation example:
-- CREATE TABLE [schema].[tablename](
-- [pkcolumn] int,
-- [pkcolumn2] int,
-- [somecolumns] NVARCHAR(10),
-- [creationDate] [datetime])
-- ON ps' + LOWER(@interval) + 'Range (creationDate)
--
-- Constraint creation example:
-- ALTER TABLE [schema].[tablename] ADD CONSTRAINT [PK_tablename] PRIMARY KEY NONCLUSTERED 
-- (
--	[pkcolumn] ASC,
--	[pkcolumn2] ASC
-- )
-- WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
-- ON ps' + LOWER(@interval) + 'Range (creationDate)'

END
ELSE IF LOWER(@dateornumeric) = 'numeric'
BEGIN
	IF @setmax IS NULL
		BEGIN
			SELECT 'Usage: EXEC usp_PartitionDatabase ''<dbname>'', ''<right_or_left>'', ''<date_or_numeric>'', ''<interval>'', ''<set_max_value>''' AS 'WARNING - SYNTAX ERROR!'
			RETURN
		END
	--Setup
	DECLARE @divisions int, @intervalint int, @PFuncRange NVARCHAR(max), @SFuncRange NVARCHAR(max)
	SELECT @intervalint = CASE WHEN @interval = 'hundreds' THEN 100 WHEN @interval = 'thousands' THEN 1000 WHEN @interval = 't_thousands' THEN 10000 WHEN @interval = 'h_thousands' THEN 100000 WHEN @interval = 'millions' THEN 1000000 END
	SET @divisions = @setmaxnum/@intervalint
	IF @divisions + @exfg > 32767 
	BEGIN
		SELECT 'Maximum number of filegroups per database may only be 32767. Please change the input parameters...' AS 'WARNING - FATAL ERROR!'
		RETURN		
	END
	IF @divisions + @exfn > 32767 
	BEGIN
		SELECT 'Maximum number of files per database may only be 32767. Please change the input parameters...' AS 'WARNING - FATAL ERROR!'
		RETURN		
	END
	SET @i = 1
	WHILE @i <> @divisions
		BEGIN
			--Filegroups and Files
			SET @sqlcmdfg = 'ALTER DATABASE [' + @dbname + '] ADD FILEGROUP FG' + CONVERT(NVARCHAR, @i*@intervalint) + ';'
			PRINT @sqlcmdfg
			SET @sqlcmdfile = 'ALTER DATABASE [' + @dbname + '] ADD FILE (NAME = ' + @dbname + '_Data_' + CONVERT(NVARCHAR, @i*@intervalint) + ', FILENAME = ''' + @filepath + '\' + @dbname + '_Data_' + CONVERT(NVARCHAR, @i*@intervalint) + '.NDF'', SIZE = 100MB, FILEGROWTH = 250MB) TO FILEGROUP FG' + CONVERT(NVARCHAR, @i*@intervalint) + ';'
			PRINT @sqlcmdfile
			SET @i = @i + 1
		END
	SET @i = 1
	WHILE @i <> @divisions
		BEGIN
			--Partition Function
			IF @i = 1
			BEGIN
				SET @PFuncRange = CONVERT(NVARCHAR, @i*@intervalint)
			END
			IF @i > 1
			BEGIN
				SET @PFuncRange = @PFuncRange + ',' + CONVERT(NVARCHAR, @i*@intervalint)
			END
			SET @i = @i + 1
		END
	SET @PFunc = 'IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = ''pf' + LOWER(@interval) + ''')
DROP PARTITION FUNCTION [pf' + LOWER(@interval) + '];
CREATE PARTITION FUNCTION [pf' + LOWER(@interval) + '] (int) AS RANGE ' + @type + ' FOR VALUES (' + @PFuncRange + ');'
	PRINT @PFunc
	SET @i = 1
	WHILE @i <> @divisions
		BEGIN
			--Partition Scheme
			IF @i = 1
			BEGIN
				SET @SFuncRange = CHAR(91) + 'FG' + CONVERT(NVARCHAR, @i*@intervalint) + CHAR(93)
			END
			IF @i > 1
			BEGIN
				SET @SFuncRange = @SFuncRange + ',' + CHAR(91) + 'FG' + CONVERT(NVARCHAR, @i*@intervalint) + CHAR(93)
			END
			SET @i = @i + 1
		END
	SET @SFunc = 'IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = ''ps' + LOWER(@interval) + 'Range'')
DROP PARTITION SCHEME ps' + LOWER(@interval) + 'Range;
CREATE PARTITION SCHEME ps' + LOWER(@interval) + 'Range AS PARTITION pf' + LOWER(@interval) + ' TO ([PRIMARY],' + @SFuncRange + ';'
	PRINT @SFunc

	PRINT '-- Table creation example:
-- CREATE TABLE [schema].[tablename](
-- [pkcolumn] int,
-- [pkcolumn2] int,
-- [somecolumns] NVARCHAR(10),
-- [serialnumber] [int])
-- ON ps' + LOWER(@interval) + 'Range (serialnumber)
--
-- Constraint creation example:
-- ALTER TABLE [schema].[tablename] ADD CONSTRAINT [PK_tablename] PRIMARY KEY NONCLUSTERED 
-- (
--	[pkcolumn] ASC,
--	[pkcolumn2] ASC
-- )
-- WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
-- ON ps' + LOWER(@interval) + 'Range (serialnumber)'

END
ELSE
BEGIN
	SELECT 'Usage: EXEC usp_PartitionDatabase ''<dbname>'', ''<right_or_left>'', ''<date_or_numeric>'', ''<interval>'', ''<set_max_value>''' AS 'WARNING - SYNTAX ERROR!'
	RETURN
END

DROP TABLE ##tblfiles