title: SQL Server Scripting Partitioning
body: <p>Had a good look on the net and books online and couldn't find an answer to my question, so here goes.</p>
  
  <p>Working on someone else's design,  I have several tables all tied to the same partition schema and partition function.  I wish to perform a split operation which would affect many hundreds of millions of rows.</p>
  
  <p>To split is no problem:</p>
  
  <pre><code>ALTER PARTITION SCHEME [ps_Scheme] NEXT USED [FG1]  ; 
  ALTER PARTITION FUNCTION [pfcn_Function]() SPLIT RANGE (20120331)
  </code></pre>
  
  <p>However, I'm concerned that this will affect many tables at once and is not desirable.</p>
  
  <p>Therefore, I was going to create a new copy of the table and do the split on a new function</p>
  
  <pre><code>CREATE PARTITION FUNCTION [pfcn_Function1](INT) 
  AS RANGE RIGHT 
  FOR VALUES 
  (
   20090101, 20090130, 20090131, 20090201...etc
  )
  
  CREATE PARTITION SCHEME [ps_Scheme1] 
  AS PARTITION [pfcn_Function1] TO 
  ([FG1], [FG2] etc
  
  CREATE TABLE [dbo].[myTableCopy]
  (
  ....
  ) ON ps_Scheme1
  </code></pre>
  
  <p>Then I would switch the partition I wish to split across:</p>
  
  <pre><code>-- The partition numbers did not align because they are based on 2 different functions.
  ALTER TABLE [Table] SWITCH PARTITION 173 TO [TableCopy] PARTITION 172
  </code></pre>
  
  <p>Finally my question is can this be automated?  You can make a copy of the table easily in SQL using SELECT INTO, but I cannot see how to automate the partitioning of the table i.e. the bit on the end of the CREATE TABLE statement that points to the partition scheme.</p>
  
  <p>Thanks for any responses.</p>