Automatically add Partition for Partitioned Tables

This stored procedure helps automatically create new partition on a new file group to an already partitioned table. I have tested the script in my testlab. Please review and let me know for any corrections if required.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sherbaz Mohamed
-- Create date: 22 September 2014
-- Description:	This SP will create a new partition whenever it is executed.
-- Also the table should have been already partitioned and
-- needs to have atleast 2 boundary values and 3 filegroups.
-- Example: sp_sherPart 'sherbaz', 'dbo', 'table1'
-- =============================================
CREATE PROCEDURE sp_sherPart 
	-- Add the parameters for the stored procedure here
	@databasename varchar(50) = '[testdb]', 
	@schemaname varchar(50) = 'dbo',
	@tablename varchar(50) = 'table1',
	@newFileLocation varchar(200) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- The script begins
	DECLARE @partitionFunction varchar(50), @partitionScheme varchar(50), @oldFilePath varchar(200)

	if object_id('tempdb..#partitionLayout') is not null drop table #partitionLayout

	-- Read the partition layout to identify the Boundary values to create the new partition.
	SELECT
		N'DatabaseName' = @databasename
		, N'SchemaName' = s.name
		, N'TableName' = o.name
		, N'IndexName' = i.name
	--    , N'IndexType' = i.type_desc
		, N'PartitionScheme' = ps.name
	--    , N'DataSpaceName' = ds.name
	--    , N'DataSpaceType' = ds.type_desc
		, N'PartitionFunction' = pf.name
		, N'PartitionNumber' = dds.destination_id
		, N'BoundaryValue' = prv.value
		, N'RightBoundary' = pf.boundary_value_on_right
			-- 1 = TRUE (values are less than boundary)
			-- 0 = FALSE (values are less than or equal to boundary)
		, N'PartitionFileGroup' = ds2.name
		, N'RowsOfData' = p.[rows]
		into #partitionLayout
	FROM
		sys.objects AS o
		INNER JOIN sys.schemas AS s
			ON o.[schema_id] = s.[schema_id]
		INNER JOIN sys.partitions AS p
			ON o.[object_id] = p.[object_id]
		INNER JOIN sys.indexes AS i
			ON p.[object_id] = i.[object_id]
			AND p.index_id = i.index_id
		INNER JOIN sys.data_spaces AS ds
			ON i.data_space_id = ds.data_space_id
		LEFT OUTER JOIN sys.partition_schemes AS ps
			ON ds.data_space_id = ps.data_space_id
		LEFT OUTER JOIN sys.partition_functions AS pf
			ON ps.function_id = pf.function_id
		LEFT OUTER JOIN sys.partition_range_values AS prv
			ON pf.function_id = prv.function_id
			AND p.partition_number = prv.boundary_id
		LEFT OUTER JOIN sys.destination_data_spaces AS dds
			ON ps.data_space_id = dds.partition_scheme_id
			AND p.partition_number = dds.destination_id
		LEFT OUTER JOIN sys.data_spaces AS ds2
			ON dds.data_space_id = ds2.data_space_id
	WHERE
		s.name = @schemaname -- schema name
		AND o.name = @tablename -- table name
	ORDER BY
		DatabaseName
		, SchemaName
		, TableName
		, IndexName
		, PartitionNumber

	select @partitionFunction = PartitionFunction, @partitionScheme = PartitionScheme from #partitionLayout

	select @partitionFunction as partitionFunction, @partitionScheme as partitionScheme;

	declare @boundaryValues TABLE(IDs int, ID INT IDENTITY(1,1));
	declare @lowerBoundary int, @upperBoundary int, @addPartition varchar(200);
	insert into @boundaryValues(IDs)
	select convert(int, Boundaryvalue) from #partitionLayout where BoundaryValue is not NULL order by BoundaryValue desc

	select @lowerBoundary=IDs from @boundaryValues where ID =2
	select @upperBoundary=IDs from @boundaryValues where ID =1

	select @lowerBoundary as lowerBoundary, @upperBoundary as upperBoundary

	-- Create the Alter Partition statement from the boundary values detected.
	set @addPartition =  'ALTER PARTITION FUNCTION ' + @partitionFunction + ' ()
	SPLIT RANGE ('+ convert(varchar(100),@upperBoundary+(@upperBoundary-@lowerBoundary)) +');'

	declare @filegroupName varchar(50), @filegroupCount int, @addFilegroup varchar(300)

	-- Read existing filegroup name and count the number of existing filegroups.
	select top 1 @filegroupName = name from sys.filegroups order by data_space_id desc
	select @filegroupCount = count(name) from sys.filegroups
	select @filegroupName as filegroupName, @filegroupCount as filegroupCount

	-- Fetch the data file location from existing data files and assign it for the new one if required.
	if @newFileLocation is null
	begin
		SELECT TOP 1 @oldFilePath=physical_name FROM sys.database_files WHERE name=@databasename;
		select @newFileLocation=rtrim(right(@oldFilePath, charindex('\', reverse(@oldFilePath)) -1))
		set @newFileLocation = replace(@oldFilePath,@newFileLocation,'')
		select @newFileLocation as newFileLocation
	end

	-- Create statments to add the new filegroup and datafile.
	set @addFilegroup = 'ALTER DATABASE ['+@databasename+'] ADD FILEGROUP ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+']
	ALTER DATABASE ['+@databasename+'] ADD FILE 
	( NAME = N'''+@databasename+'_'+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+''',
	 FILENAME = N'''+@newFileLocation+'\'+@databasename+'_'+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+'.ndf'')
	  TO FILEGROUP ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+']'

	print @addFileGroup
	EXECUTE(@addFileGroup)

	-- Create statements to tell SQL about the NEXT USED filgroup information.
	declare @alterPartitionScheme varchar(200)
	set @alterPartitionScheme = 'ALTER PARTITION SCHEME '+@partitionScheme+'
	NEXT USED ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+'];'

	PRINT @alterPartitionScheme
	EXECUTE(@alterPartitionScheme)

	print @addPartition
	EXECUTE(@addPartition)
END
GO

If you would like to test it in your testlab, you are free to make use of below commands to create the test database, files and partitions. Run below script with care step by step understanding what each set does.

USE [master]
GO
create database sherbaz
go

ALTER DATABASE [sherbaz] ADD FILEGROUP [FG_2]
GO
ALTER DATABASE [sherbaz] ADD FILE 
( NAME = N'sherbaz_fg2', FILENAME = N'M:\Program Files\Microsoft SQL Server\MSSQL11.INST01\MSSQL\DATA\sherbaz_fg2.ndf') TO FILEGROUP [FG_2]
GO
ALTER DATABASE [sherbaz] ADD FILEGROUP [FG_3]
GO
ALTER DATABASE [sherbaz] ADD FILE
( NAME = N'sherbaz_fg3', FILENAME = N'M:\Program Files\Microsoft SQL Server\MSSQL11.INST01\MSSQL\DATA\sherbaz_fg3.ndf') TO FILEGROUP [FG_3]
GO
ALTER DATABASE [sherbaz] ADD FILEGROUP [FG_4]
GO
ALTER DATABASE [sherbaz] ADD FILE 
( NAME = N'sherbaz_fg4', FILENAME = N'M:\Program Files\Microsoft SQL Server\MSSQL11.INST01\MSSQL\DATA\sherbaz_fg4.ndf') TO FILEGROUP [FG_4]
GO

use sherbaz
go

--To create partition function on integer column
--drop partition function PartitionFunction
CREATE PARTITION FUNCTION [PartitionFunction](int) AS 
RANGE LEFT FOR VALUES (1,100)
GO

--To Create partition function on datetime column
CREATE PARTITION FUNCTION [PartitionFunction](datetime) AS 
RANGE LEFT FOR VALUES ('2014-01-22 16:31:09.480','2014-02-22 16:31:09.480')
GO


--drop partition scheme PartitionScheme
CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION
[PartitionFunction] TO ([PRIMARY],FG_2,FG_3)
GO

--drop table table1
--Create table with integer column
create table table1
(
 id int not null,
 text varchar(200)
)
on PartitionScheme(id)
go

--Create table with datetime column
create table table1
(
 id datetime not null,
 text varchar(200)
)
on PartitionScheme(id)
go

-- Add index on the partioned column
ALTER TABLE table1 ADD CONSTRAINT PrimaryKey PRIMARY KEY
(id ASC)
go


--To populate some data in the table for partitioning on integer column.
declare @i int
set @i=-100
while @i<500
begin
insert into table1 (id, [text]) values(@i,RAND())
set @i=@i+1;
end
go

select getdate()

-- Insert data to table to partition on datetime column
insert into table1 (id, [text]) values('2013-3-22 16:53:40.227',RAND())

--See what's in the table.
select * from table1