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