title: How does one Remove a Partition from a Table?
body: <p>I have managed to add a Partition to a Table (Logs) but needed to create a Rollback script in case it needs to be removed.  Unfortunately, this has now failed and Logs now has no primary key as a result of failing part-way through the rollback script and I have no way to add it back as I get the error...</p>
  
  <blockquote>
    <p>Column 'SuperLogId' is partitioning column of the index 'PK__Logs__0E6B88F2'. Partition columns for a unique index must be a subset of the index key.</p>
  </blockquote>
  
  <p>when trying to run this:</p>
  
  <pre><code>ALTER TABLE dbo.Logs
  ADD PRIMARY KEY CLUSTERED (Id ASC)
  </code></pre>
  
  <p>So I tried following this guide (<a href="https://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html" rel="nofollow noreferrer">https://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html</a>) and ended up having to write this to generate a script to merge all my dynamically-created partitions.</p>
  
  <pre><code>DECLARE @partitionsTable dbo.NVarCharCollectionTableType --User-defined table type to hold a collection of NVarChars.
  
  INSERT INTO @partitionsTable
  SELECT CONCAT('ALTER PARTITION FUNCTION Logs_SuperLogId_PartitionFunction() MERGE RANGE (', CONVERT(NVARCHAR, [Value]), ')')
  FROM SYS.PARTITION_SCHEMES
  INNER JOIN SYS.PARTITION_FUNCTIONS ON PARTITION_FUNCTIONS.FUNCTION_ID = PARTITION_SCHEMES.FUNCTION_ID
  INNER JOIN SYS.PARTITION_RANGE_VALUES ON PARTITION_RANGE_VALUES.FUNCTION_ID = PARTITION_FUNCTIONS.FUNCTION_ID
  WHERE PARTITION_SCHEMES.Name = 'Logs_SuperLogId_PartitionScheme'
  AND PARTITION_FUNCTIONS.Name = 'Logs_SuperLogId_PartitionFunction'
  ORDER BY [Value] ASC
  
  DECLARE @statement NVARCHAR(MAX)
  
  SELECT @statement =
      CASE
          WHEN @statement IS NULL
          THEN CAST([Text] AS NVARCHAR(MAX))
          ELSE CONCAT(@statement, '; ', [Text])
      END
      FROM @partitionsTable
      ORDER BY [Text] ASC
  
  SELECT @statement
  
  EXECUTE SP_EXECUTESQL @statement
  
  ALTER PARTITION SCHEME Logs_SuperLogId_PartitionScheme NEXT USED [PRIMARY]
  </code></pre>
  
  <p>The guide suggested this would help somehow but it didn't!  I still get the same error when trying to re-add the Primary Key and still get these errors for trying to drop the Partition Function and Partition Scheme!</p>
  
  <pre><code>DROP PARTITION SCHEME Logs_SuperLogId_PartitionScheme
  </code></pre>
  
  <blockquote>
    <p>The partition scheme "Logs_SuperLogId_PartitionScheme" is currently being used to partition one or more tables.</p>
  </blockquote>
  
  <pre><code>DROP PARTITION FUNCTION CatLogs_CatSessionLogId_PartitionFunction
  </code></pre>
  
  <blockquote>
    <p>Partition function 'Logs_SuperLogId_PartitionFunction' is being used by one or more partition schemes.</p>
  </blockquote>
  
  <p>How is my Partition Scheme still being used?  Why can't I just get rid of it and it be not used anymore?  I just want to de-partition my Logs table and re-add its original clustered primary key (which I had to previously remove and replace with a non-clustered primary key to make SuperLogId have a clustered index on it so it could be partitioned upon).</p>
  
  <p><strong>Update:</strong></p>
  
  <p>I was able to use the following hack to get the Partition removed from my table but I still can't drop the Partition Scheme or Function.</p>
  
  <pre><code>--HACK: Dummy Index to disassociate the table from the partitioning scheme.
  CREATE CLUSTERED INDEX IX_Logs_Id ON dbo.Logs(Id) ON [Primary]
  
  --Now that the table has been disassociated with the partition, this dummy index can be dropped.
  DROP INDEX IX_Logs_Id ON dbo.Logs
  </code></pre>
  
  <p>I have since ran this script to find out which tables are using any Partitions in my database and it returns nothing, as expected.</p>
  
  <pre><code>SELECT DISTINCT TABLES.NAME
  FROM SYS.PARTITIONS
  INNER JOIN SYS.TABLES ON PARTITIONS.OBJECT_ID = TABLES.OBJECT_ID
  WHERE PARTITIONS.PARTITION_NUMBER &lt;&gt; 1
  </code></pre>
  
  <p>This allowed me to re-add the Primary key but I still get the <code>The partition scheme "Logs_SuperLogId_PartitionScheme" is currently being used...</code> error when trying to drop the Partition Scheme.</p>
  
  <p>Based on the Microsoft documentation (<a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-partition-scheme-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-partition-scheme-transact-sql?view=sql-server-2017</a>), the Partition Scheme should be droppable if there are no tables or indices references it.  Therefore I subsequently also ran this script to check for an index using it...</p>
  
  <pre><code>SELECT DISTINCT indexes.NAME
  FROM SYS.PARTITIONS
  INNER JOIN SYS.indexes ON indexes.index_id = partitions.index_id
  WHERE PARTITIONS.PARTITION_NUMBER &lt;&gt; 1
  </code></pre>
  
  <p>...And it returned nothing!  So what on earth is using my Partition Scheme?!</p>