Table Partition Remove part 9

Remove Table Partition.

--Remove table partition completely

-- Drop PK or CI
DROP INDEX Cx_tbl_partition_test on [tbl_partition_test] /* drop the clustered if already exists if not create new CI on primary FG*/

ALTER TABLE [dbo].[tbl_partition_test] DROP CONSTRAINT [PK_C1810429_tbl_partition_test] WITH ( ONLINE = OFF )

/* Create clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]"  or Dummy part is key to removing the partition scheme from the table ! */

CREATE CLUSTERED INDEX Cx_tbl_partition_test ON [tbl_partition_test] (ID,Date_Col) ON FG_Dummy 

DROP PARTITION SCHEME PS_myPartitionScheme

DROP PARTITION FUNCTION PF_myDateRange

/* drop the dummy clustered index you created, as it was only created to free the table from the partitioning scheme */

DROP INDEX Cx_tbl_partition_test ON [tbl_partition_test]

--Create the original PK
ALTER TABLE [dbo].[tbl_partition_test] ADD  CONSTRAINT [PK_C1810429_tbl_partition_test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date_Col] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PS_myPartitionScheme]([Date_Col])
GO