How to drop partition scheme without dropping the table

• aYasser97

  SSC Enthusiast

  Points: 181

  More actions

  September 22, 2022 at 4:31 pm

  #4090881

  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.

  Attachments:

  You must be logged in to view attached files.

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  September 22, 2022 at 8:16 pm

  #4090948

  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

• October 17, 2022 at 7:52 am

  This was removed by the editor as SPAM