Drop partition scheme with orphan indexes

Question

I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted.

I can't delete the scheme, since it appears to still be used by the indexes, even though they don't exist anymore.

Running the following query:

select object_name(i.object_id), i.name, i.index_id
FROM   sys.indexes i
JOIN   sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE  ps.name = 'CallScheme'

I get the names of the 2 tables that had the indexes, but the index names are NULL (and index id is 0).

How do I get rid of these indexes so that I can drop the partition scheme?

Answer 1

but the index names are NULL (and index id is 0)

That's because they are heaps. BOL Reference on sys.indexes

One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme. Then once you have those tables off of that partition scheme you should be able to continue with removing it.

create clustered index IX_YourIndex
on dbo.YourTable (YourCiKeyCol)
on SomeOtherFilegroup;