How to drop partition scheme without dropping the table

  • Right now, I'm getting the following error when I try to drop the partitioning scheme: The partition scheme "zzz" is currently being used to partition one or more tables.

    I did drop the clustered index created on the column used to partition.

    Attached is all the indices on that table. As you can see, there are no indexes that use the partition scheme. Are heaps causing this error? If so, how do I drop them? I also do not want to drop the table just to remove the partition scheme (if possible).

    Any help would be much appreciated.

    You must be logged in to view attached files.
  • A Data Space is either a Filegroup or a Partition Scheme. Everything showing in your result with a Data Space Name that isn't a filegroup (or has a data_space_id that doesn't map to a filegroup_id in sys.filegroups) is partitioned, and must be moved off the partitions before you can drop the Partition Function and Scheme.

    You will need to re-CREATE each non-filegroup index on a filegroup instead of the partition (use CREATE INDEX even though the index already exists; include WITH (DROP_EXISTING = ON))

    CREATE [CLUSTERED|NONCLUSTERED] INDEX[...] ON [<table_name>](col, col, col...)
    WITH (DROP_EXISTING = ON, <other_index_option>, <other_index_option>, <other_index_option>, ...)
    ON [<filegroup_name>];

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply