March 1, 2010 at 6:48 pm
Hi
I am creating
CREATE PARTITION FUNCTION my_pfn(datetime)
AS RANGE RIGHT FOR VALUES
( '20100401' --Apr 2010
, '20100501' --May 2010
, '20100601' --Jun 2010
)
GO
CREATE PARTITION SCHEME my_psch
AS PARTITION my_pfn TO
(
[FG_Month01]
,[FG_Month02]
,[FG_Month03]
,[PRIMARY]
)
GO
I understand that i can look at system views for the distribution. Is there any query which will enable me to recreate the SQL?
TIA
March 2, 2010 at 5:34 am
I am not sure what you actually looking for?
following will give you the details of the created partition fucntions.
SELECT * FROM sys.partition_functions
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 2, 2010 at 11:56 am
This only gives the name, function_id, type , type_desc , fanout, boundary_value_on_right etc. How do i regenerate partition function script from the database like the one i ran above?
Thanks
March 3, 2010 at 9:51 am
Go to that database-->Storage-->Partition Functions-->Your function(right click and script it out).
MJ
March 10, 2010 at 3:25 pm
[font="Courier New"][font="Courier New"]Thanks for all who responded to my earlier question.
This is what i was looking. It took me a while to come up with this script. I am posting it here so that someone else might benefit from it
/****** Object: PartitionFunction [my_prth_fn] */
/*
CREATE PARTITION FUNCTION [my_prth_fn](datetime)
AS RANGE RIGHT FOR VALUES (N'2010-04-01T00:00:00.000'
, N'2010-05-01T00:00:00.000', N'2010-06-01T00:00:00.000'
, N'2010-07-01T00:00:00.000', N'2010-08-01T00:00:00.000'
, N'2010-09-01T00:00:00.000', N'2010-10-01T00:00:00.000'
, N'2010-11-01T00:00:00.000', N'2010-12-01T00:00:00.000'
, N'2011-01-01T00:00:00.000', N'2011-02-01T00:00:00.000'
, N'2011-03-01T00:00:00.000', N'2011-04-01T00:00:00.000'
, N'2011-05-01T00:00:00.000', N'2011-06-01T00:00:00.000'
, N'2011-07-01T00:00:00.000', N'2011-08-01T00:00:00.000'
, N'2011-09-01T00:00:00.000', N'2011-10-01T00:00:00.000'
, N'2011-11-01T00:00:00.000', N'2011-12-01T00:00:00.000'
, N'2012-01-01T00:00:00.000', N'2012-02-01T00:00:00.000'
, N'2012-03-01T00:00:00.000')
GO
*/
------- CODE TO REGENERATE -------
SELECT 1 AS seq , 'CREATE PARTITION FUNCTION [' + pf.name + '] ('
+ t.name
+ ') AS ' + CAST(pf.type_desc AS VARCHAR(20)) COLLATE SQL_Latin1_General_CP1_CI_AS
+ CASE pf.boundary_value_on_right
WHEN 1 THEN ' RIGHT ' + ' FOR VALUES ('
WHEN 0 THEN ' LEFT ' + ' FOR VALUES ('
ELSE ' <UNDEFINED RANGE> FOR VALUES ('
END AS CMD
INTO #TMP
FROM sys.partition_functions pf
,sys.partition_parameters pp
,sys.types t
WHERE pf.name = 'my_prth_fn'
AND pp.function_id = pf.function_id
AND pp.system_type_id = t.system_type_id
GO
DECLARE
@buf_seq BIGINT
,@buf_cmd VARCHAR(512)
DECLARE C1 CURSOR FOR
SELECT
2 AS seq,
CASE prv.boundary_id WHEN 1 THEN ' N'''
ELSE ',N'''
END +
CAST(prv.value as VARCHAR(20))+'''' AS CMD
FROM sys.partition_range_values prv
,sys.partition_functions pf
WHERE pf.name = 'my_prth_fn'
AND pf.function_id = prv.function_id
ORDER BY pf.name , prv.boundary_id
OPEN C1
FETCH NEXT FROM C1 INTO @buf_seq , @buf_cmd
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO #TMP (SEQ, CMD) VALUES (@buf_seq , @buf_cmd)
FETCH NEXT FROM C1 INTO @buf_seq , @buf_cmd
END
CLOSE C1
DEALLOCATE C1
INSERT INTO #TMP (SEQ, CMD) VALUES (3 , ')' )
INSERT INTO #TMP (SEQ, CMD) VALUES (4 , 'GO' )
GO
SELECT CMD FROM #TMP ORDER BY seq
GO
DROP TABLE #TMP
GO
--**********************************************
--********************OUTPUT********************
--**********************************************
CREATE PARTITION FUNCTION [my_prth_fn] (datetime) AS RANGE RIGHT FOR VALUES (
N'Apr 1 2010 12:00AM'
,N'May 1 2010 12:00AM'
,N'Jun 1 2010 12:00AM'
,N'Jul 1 2010 12:00AM'
,N'Aug 1 2010 12:00AM'
,N'Sep 1 2010 12:00AM'
,N'Oct 1 2010 12:00AM'
,N'Nov 1 2010 12:00AM'
,N'Dec 1 2010 12:00AM'
,N'Jan 1 2011 12:00AM'
,N'Feb 1 2011 12:00AM'
,N'Mar 1 2011 12:00AM'
,N'Apr 1 2011 12:00AM'
,N'May 1 2011 12:00AM'
,N'Jun 1 2011 12:00AM'
,N'Jul 1 2011 12:00AM'
,N'Aug 1 2011 12:00AM'
,N'Sep 1 2011 12:00AM'
,N'Oct 1 2011 12:00AM'
,N'Nov 1 2011 12:00AM'
,N'Dec 1 2011 12:00AM'
,N'Jan 1 2012 12:00AM'
,N'Feb 1 2012 12:00AM'
,N'Mar 1 2012 12:00AM'
)
GO
(27 row(s) affected)
[/font][/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply