changing function os partitioned table

[5HAUN7]

Hi,
I have a big table which is partitioned , I need to change the portioning function (including the column used by partition key). I don’t want to change the table or use a temp table as there are a lot of dependent objects to this table

I wrote:

DECLARE @partition_count INT,@cmd VARCHAR(8000)
SELECT @partition_count=MAX(p.partition_Number) FROM sys.allocation_units A,sys.partitions P ,sys.data_spaces D
WHERE P.OBJECT_ID=OBJECT_ID('TABLEA')
AND A.container_id=P.Partition_id
AND A.data_space_id=D.data_space_id
SELECT @partition_count

WHILE (@partition_count>1)
BEGIN
SELECT @cmd='ALTER PARTITION FUNCTION MainPartitionFunction() MERGE range ('+CONVERT(VARCHAR(10),@partition_count-1)+')'
exec (@cmd)
SELECT @partition_count=@partition_count-1
End

Which will merge all the existing partitions to one. But I don’t seem to be able to find a way change the existing funtion. ( in need to change the partition key all together)
Is there a way to disable or drop parition of the table all together (convert the table to a non-partitioned table) and then I can re define the parition function again.
Any ideas?
Shaunt

[rmiao]

Is it for sql2k5?

[5HAUN7]

yes and I don't think table partitioning is possible on 2000

[rmiao]

Available in other rdbms.

[5HAUN7]

of course, appologies for my ignorant , it is SQL 2005+sp2

[rmiao]

ALTER PARTITION FUNCTION can only be used for splitting one partition into two, or merging two partitions into one. To change the way a table is partitioned, you have to repartition it. Books online listed several options to do that under 'Modifying Partitioned Tables and Indexes'.