We need to remove the partition that has been implemented into a table in 2005. Could anyone help on the same. While trying to remove the Partition Scheme, I am getting the following error message: Msg 7717, Level 16, State 1, Line 1 The partition scheme "PS_ActivityYear" is currently being used to partition one or more tables.
Could anyone explain how to remove the partition?
Thanks in advance. Anandan
asked Dec 09, 2011 at 12:11 AM in Default
You have to rebuild all of your indexes that are using the partition schema, and specify a filegroup instead of the partition schema.
If you need to find all the indexes that are using the partition schema you can use the following code:
I didn't have my own script with me so this is actually copied from : http://sqlfascination.com/2010/01/12/dynamic-partitioning-what-objects-are-using-a-partition-schema-sql-tuesday-002/
All credits to Andrew Hogg, read his post, it may give you some ideas on how all the partition objects relate to each other.