Removing Partitions

Remembering that indexes are partitioned not tables, I used this query to list them …

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS [schema],
       OBJECT_NAME(i.object_id) AS [object],
       i.name AS [index],
       s.name AS [partition_scheme]
FROM sys.indexes i
JOIN sys.partition_schemes s
  ON i.data_space_id = s.data_space_id;

I found the quickest way to remove partitioning is :-

• Make a copy of the partitioned table with all the data (EG: “tablename2”)
• Recreate all indexes, keys, and constraints (specifying the “ON [PRIMARY]” option)
• Drop the partitioned table
• Rename the copy to the table that was just dropped
• Drop any partition schemas and functions

SELECT * FROM sys.partition_schemes
--DROP PARTITION SCHEME somename

SELECT * FROM sys.partition_functions
--DROP PARTITION FUNCTION somename