tech.guitarrapc.cóm

Technical updates

PowerShellでMySQLのパーティション構文を生成する

謎社ではAWSを活用しています。 特に、RDS (Relational Database Service) でのMySQLは素敵です。 ほめる点ばかりではありませんが、良く考えられています。 さて、MySQLの負荷分散といえば、垂直分割 (DB単位、テーブル単位で分割) と 水平分割 (テーブルを行単位で分割) です。 垂直分割はシャーディング (DB sharding) 、 水平分割はパーティショニング (Partitioning) とも言われます。 この辺は、専門のこわい方々は多いので置いておいて、パーティション用のSQL構文をPowerShellで自動生成させたのでスクリプトの紹介です。 ※今回のパーティショニングは、日付をキーにしたレンジパーティショニングです。 先に結論を
「手書きコワイ、めんどくさい、やってられない、そうだDRYだ!」

サンプルデータ

サンプルコードはGitで公開しています。
PowerShellUtil / MySQLPartition

利用テーブル

以下のテストテーブルに対して、created_dt をベースに毎日(Daily)レンジパーティションを切りましょう。
CREATE TABLE hoge (
	id INT NOT NULL AUTO_INCREMENT,
	hoge VARCHAR(32) NOT NULL,
	history_id INT NOT NULL,
	created_dt DATETIME NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (history_id,)
) ENGINE=INNODB;

パーティショニングの手順

0ベースからパーティショニングを行う場合は、まずテーブル構造を見直す必要があるか確認が必要です。 ご存じのとおり、Primary Key、全Unique Key にパーティションキーが含まれる必要があるので。 よって、以下の手順で考えましょう。
  1. Primary Keyの張り直し
  2. Unique Keyの張り直し
  3. パーティション実行
  4. パーティション追加
  5. パーティションドロップ

1. Primary Keyの張り直し

まずは、Primary Keyの張替が必要だった前提で。 RemapPKSQL_hoge.sql
param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string[]]$key
)

function Get-RemapPKsql{

    [CmdletBinding()]
    param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string[]]$key
    )

    begin
    {
    }

    process
    {

        $output = "ALTER TABLE "
        $output += $table
        $output += " DROP PRIMARY KEY `n"


        $output += ", ADD PRIMARY KEY "
        $output += "("

        $output += $key | %{$_ + " ,"}
        $beforeLast = $output.Length -1
        $output = $output.Substring(0,$beforeLast)

        $output += ");"
    }

    end
    {
        $output
    }
}

Get-RemapPKsql -table $table -key $key `
    | Out-File -FilePath .\RemapPKSQL_$table.sql -Encoding default
バッチから実行してみます。 テーブル名とPrimary Keyにしたいカラムを渡すだけです。 run_RemapPK.bat
powershell .\run_RemapPK.ps1 -table "hoge" -key "id", "created"

pause
以下のSQLが生成されます。 RemapPKSQL_hoge.sql
ALTER TABLE hoge DROP PRIMARY KEY
, ADD PRIMARY KEY (id , created );

2. Unique Keyの張り直し

次にUniqueキーにパーティションキーを含めましょう。 run_NewDropUniqueKey_sqlOutput.ps1
param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$keyName,
    [parameter(Mandatory=$true)]
    [string[]]$key
)

function Get-NewDropUniqueKeysql{

    [CmdletBinding()]
    param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$keyName,
    [parameter(Mandatory=$true)]
    [string[]]$key
    )

    begin
    {
    }

    process
    {

        $output = "ALTER TABLE "
        $output += $table

        $output += " ADD UNIQUE "
        $output += ("'" + $keyName +"_2'")
        $output += "("

        $output += $key | %{$_ + " ,"}
        $beforeLast = $output.Length -1
        $output = $output.Substring(0,$beforeLast)

        $output += "); `n"


        $output += "ALTER TABLE "
        $output += $table

        $output += " DROP INDEX "
        $output += $keyName

        $output += "; `n"
    }

    end
    {
        $output
    }
}

Get-NewDropUniqueKeysql -table $table -keyName $keyName -key $key `
    | Out-File -FilePath .\NewDropUniqueKeySQL_$table.sql -Encoding default
バッチから実行してみます。 テーブル名とUnique Keyにしたいカラムを渡すだけです。 run_NewDropUniqueKey_sqlOutput.bat
powershell .\run_NewDropUniqueKey_sqlOutput.ps1 -table "hoge" -keyname "history_id" -key "history_id", "created"

pause
以下のSQLが生成されます。 ※場合に ADD UNIQUEでキー名称ではじかれることがあるので、その際は'キー名'を消します。 NewDropUniqueKeySQL_hoge.sql
ALTER TABLE hoge ADD UNIQUE 'history_id_2'(history_id , created );
ALTER TABLE hoge DROP INDEX history_id;

3. パーティション実行

ではパーティションを張ってみます。 run_CreatePartition_sqlOutput.ps1
param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
)

function Get-PartitionSQLbyWeekday{

    [CmdletBinding()]
    param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
    )

    begin
    {
    }

    process
    {

        $output = @"
ALTER TABLE $table
    PARTITION BY RANGE (TO_DAYS($column))( `n
"@

        $output += $daysAdd `
            | %{$(Get-Date).AddDays($_)} `
            | %{"`t`t PARTITION p{3}{4:d2}{5:d2} VALUES LESS THAN (TO_DAYS('{0}-{1:d2}-{2:d2} 00:00:00')) COMMENT = '{3}-{4:d2}-{5:d2}',`n" `
                -f  $_.Year,
                    $_.Month,
                    $_.Day,
                    $_.AddDays("-1").Year,
                    $_.AddDays("-1").Month,
                    $_.AddDays("-1").Day}

        $beforeLast = $output.Length -2
        $output = $output.Substring(0,$beforeLast)
        $output += "`n"
        $output += "); "
    }

    end
    {
        $output
    }
}

Get-PartitionSQLbyWeekday -table $table -column $column -daysAdd $daysAdd `
    | Out-File -FilePath .\CreatePartitionSQL_$table.sql -Encoding default
バッチから実行してみます。 テーブル名と、現在の日付から見て何日前から何日までの日数を渡すだけです。 テーブルデータを網羅するように、パーティションキーに指示します。(今回は、全日付が含まれることが必須) run_CreatePartition_sqlOutput.bat
powershell .\run_CreatePartition_sqlOutput.ps1 -table "hoge" -column "created" -daysAdd (-81..3)

pause
指定した日付に応じて、以下のSQLが生成されます。 CreatePartitionSQL_hoge.sql
ALTER TABLE hoge
    PARTITION BY RANGE (TO_DAYS(created))(
		 PARTITION p20130124 VALUES LESS THAN (TO_DAYS('2013-01-25 00:00:00')) COMMENT = '2013-01-24',
 		 PARTITION p20130125 VALUES LESS THAN (TO_DAYS('2013-01-26 00:00:00')) COMMENT = '2013-01-25',
 		 PARTITION p20130126 VALUES LESS THAN (TO_DAYS('2013-01-27 00:00:00')) COMMENT = '2013-01-26',
 		 PARTITION p20130127 VALUES LESS THAN (TO_DAYS('2013-01-28 00:00:00')) COMMENT = '2013-01-27',
 		 PARTITION p20130128 VALUES LESS THAN (TO_DAYS('2013-01-29 00:00:00')) COMMENT = '2013-01-28',
 		 PARTITION p20130129 VALUES LESS THAN (TO_DAYS('2013-01-30 00:00:00')) COMMENT = '2013-01-29',
 		 PARTITION p20130130 VALUES LESS THAN (TO_DAYS('2013-01-31 00:00:00')) COMMENT = '2013-01-30',
 		 PARTITION p20130131 VALUES LESS THAN (TO_DAYS('2013-02-01 00:00:00')) COMMENT = '2013-01-31',
 		 PARTITION p20130201 VALUES LESS THAN (TO_DAYS('2013-02-02 00:00:00')) COMMENT = '2013-02-01',
 		 PARTITION p20130202 VALUES LESS THAN (TO_DAYS('2013-02-03 00:00:00')) COMMENT = '2013-02-02',
 		 PARTITION p20130203 VALUES LESS THAN (TO_DAYS('2013-02-04 00:00:00')) COMMENT = '2013-02-03',
 		 PARTITION p20130204 VALUES LESS THAN (TO_DAYS('2013-02-05 00:00:00')) COMMENT = '2013-02-04',
 		 PARTITION p20130205 VALUES LESS THAN (TO_DAYS('2013-02-06 00:00:00')) COMMENT = '2013-02-05',
 		 PARTITION p20130206 VALUES LESS THAN (TO_DAYS('2013-02-07 00:00:00')) COMMENT = '2013-02-06',
 		 PARTITION p20130207 VALUES LESS THAN (TO_DAYS('2013-02-08 00:00:00')) COMMENT = '2013-02-07',
 		 PARTITION p20130208 VALUES LESS THAN (TO_DAYS('2013-02-09 00:00:00')) COMMENT = '2013-02-08',
 		 PARTITION p20130209 VALUES LESS THAN (TO_DAYS('2013-02-10 00:00:00')) COMMENT = '2013-02-09',
 		 PARTITION p20130210 VALUES LESS THAN (TO_DAYS('2013-02-11 00:00:00')) COMMENT = '2013-02-10',
 		 PARTITION p20130211 VALUES LESS THAN (TO_DAYS('2013-02-12 00:00:00')) COMMENT = '2013-02-11',
 		 PARTITION p20130212 VALUES LESS THAN (TO_DAYS('2013-02-13 00:00:00')) COMMENT = '2013-02-12',
 		 PARTITION p20130213 VALUES LESS THAN (TO_DAYS('2013-02-14 00:00:00')) COMMENT = '2013-02-13',
 		 PARTITION p20130214 VALUES LESS THAN (TO_DAYS('2013-02-15 00:00:00')) COMMENT = '2013-02-14',
 		 PARTITION p20130215 VALUES LESS THAN (TO_DAYS('2013-02-16 00:00:00')) COMMENT = '2013-02-15',
 		 PARTITION p20130216 VALUES LESS THAN (TO_DAYS('2013-02-17 00:00:00')) COMMENT = '2013-02-16',
 		 PARTITION p20130217 VALUES LESS THAN (TO_DAYS('2013-02-18 00:00:00')) COMMENT = '2013-02-17',
 		 PARTITION p20130218 VALUES LESS THAN (TO_DAYS('2013-02-19 00:00:00')) COMMENT = '2013-02-18',
 		 PARTITION p20130219 VALUES LESS THAN (TO_DAYS('2013-02-20 00:00:00')) COMMENT = '2013-02-19',
 		 PARTITION p20130220 VALUES LESS THAN (TO_DAYS('2013-02-21 00:00:00')) COMMENT = '2013-02-20',
 		 PARTITION p20130221 VALUES LESS THAN (TO_DAYS('2013-02-22 00:00:00')) COMMENT = '2013-02-21',
 		 PARTITION p20130222 VALUES LESS THAN (TO_DAYS('2013-02-23 00:00:00')) COMMENT = '2013-02-22',
 		 PARTITION p20130223 VALUES LESS THAN (TO_DAYS('2013-02-24 00:00:00')) COMMENT = '2013-02-23',
 		 PARTITION p20130224 VALUES LESS THAN (TO_DAYS('2013-02-25 00:00:00')) COMMENT = '2013-02-24',
 		 PARTITION p20130225 VALUES LESS THAN (TO_DAYS('2013-02-26 00:00:00')) COMMENT = '2013-02-25',
 		 PARTITION p20130226 VALUES LESS THAN (TO_DAYS('2013-02-27 00:00:00')) COMMENT = '2013-02-26',
 		 PARTITION p20130227 VALUES LESS THAN (TO_DAYS('2013-02-28 00:00:00')) COMMENT = '2013-02-27',
 		 PARTITION p20130228 VALUES LESS THAN (TO_DAYS('2013-03-01 00:00:00')) COMMENT = '2013-02-28',
 		 PARTITION p20130301 VALUES LESS THAN (TO_DAYS('2013-03-02 00:00:00')) COMMENT = '2013-03-01',
 		 PARTITION p20130302 VALUES LESS THAN (TO_DAYS('2013-03-03 00:00:00')) COMMENT = '2013-03-02',
 		 PARTITION p20130303 VALUES LESS THAN (TO_DAYS('2013-03-04 00:00:00')) COMMENT = '2013-03-03',
 		 PARTITION p20130304 VALUES LESS THAN (TO_DAYS('2013-03-05 00:00:00')) COMMENT = '2013-03-04',
 		 PARTITION p20130305 VALUES LESS THAN (TO_DAYS('2013-03-06 00:00:00')) COMMENT = '2013-03-05',
 		 PARTITION p20130306 VALUES LESS THAN (TO_DAYS('2013-03-07 00:00:00')) COMMENT = '2013-03-06',
 		 PARTITION p20130307 VALUES LESS THAN (TO_DAYS('2013-03-08 00:00:00')) COMMENT = '2013-03-07',
 		 PARTITION p20130308 VALUES LESS THAN (TO_DAYS('2013-03-09 00:00:00')) COMMENT = '2013-03-08',
 		 PARTITION p20130309 VALUES LESS THAN (TO_DAYS('2013-03-10 00:00:00')) COMMENT = '2013-03-09',
 		 PARTITION p20130310 VALUES LESS THAN (TO_DAYS('2013-03-11 00:00:00')) COMMENT = '2013-03-10',
 		 PARTITION p20130311 VALUES LESS THAN (TO_DAYS('2013-03-12 00:00:00')) COMMENT = '2013-03-11',
 		 PARTITION p20130312 VALUES LESS THAN (TO_DAYS('2013-03-13 00:00:00')) COMMENT = '2013-03-12',
 		 PARTITION p20130313 VALUES LESS THAN (TO_DAYS('2013-03-14 00:00:00')) COMMENT = '2013-03-13',
 		 PARTITION p20130314 VALUES LESS THAN (TO_DAYS('2013-03-15 00:00:00')) COMMENT = '2013-03-14',
 		 PARTITION p20130315 VALUES LESS THAN (TO_DAYS('2013-03-16 00:00:00')) COMMENT = '2013-03-15',
 		 PARTITION p20130316 VALUES LESS THAN (TO_DAYS('2013-03-17 00:00:00')) COMMENT = '2013-03-16',
 		 PARTITION p20130317 VALUES LESS THAN (TO_DAYS('2013-03-18 00:00:00')) COMMENT = '2013-03-17',
 		 PARTITION p20130318 VALUES LESS THAN (TO_DAYS('2013-03-19 00:00:00')) COMMENT = '2013-03-18',
 		 PARTITION p20130319 VALUES LESS THAN (TO_DAYS('2013-03-20 00:00:00')) COMMENT = '2013-03-19',
 		 PARTITION p20130320 VALUES LESS THAN (TO_DAYS('2013-03-21 00:00:00')) COMMENT = '2013-03-20',
 		 PARTITION p20130321 VALUES LESS THAN (TO_DAYS('2013-03-22 00:00:00')) COMMENT = '2013-03-21',
 		 PARTITION p20130322 VALUES LESS THAN (TO_DAYS('2013-03-23 00:00:00')) COMMENT = '2013-03-22',
 		 PARTITION p20130323 VALUES LESS THAN (TO_DAYS('2013-03-24 00:00:00')) COMMENT = '2013-03-23',
 		 PARTITION p20130324 VALUES LESS THAN (TO_DAYS('2013-03-25 00:00:00')) COMMENT = '2013-03-24',
 		 PARTITION p20130325 VALUES LESS THAN (TO_DAYS('2013-03-26 00:00:00')) COMMENT = '2013-03-25',
 		 PARTITION p20130326 VALUES LESS THAN (TO_DAYS('2013-03-27 00:00:00')) COMMENT = '2013-03-26',
 		 PARTITION p20130327 VALUES LESS THAN (TO_DAYS('2013-03-28 00:00:00')) COMMENT = '2013-03-27',
 		 PARTITION p20130328 VALUES LESS THAN (TO_DAYS('2013-03-29 00:00:00')) COMMENT = '2013-03-28',
 		 PARTITION p20130329 VALUES LESS THAN (TO_DAYS('2013-03-30 00:00:00')) COMMENT = '2013-03-29',
 		 PARTITION p20130330 VALUES LESS THAN (TO_DAYS('2013-03-31 00:00:00')) COMMENT = '2013-03-30',
 		 PARTITION p20130331 VALUES LESS THAN (TO_DAYS('2013-04-01 00:00:00')) COMMENT = '2013-03-31',
 		 PARTITION p20130401 VALUES LESS THAN (TO_DAYS('2013-04-02 00:00:00')) COMMENT = '2013-04-01',
 		 PARTITION p20130402 VALUES LESS THAN (TO_DAYS('2013-04-03 00:00:00')) COMMENT = '2013-04-02',
 		 PARTITION p20130403 VALUES LESS THAN (TO_DAYS('2013-04-04 00:00:00')) COMMENT = '2013-04-03',
 		 PARTITION p20130404 VALUES LESS THAN (TO_DAYS('2013-04-05 00:00:00')) COMMENT = '2013-04-04',
 		 PARTITION p20130405 VALUES LESS THAN (TO_DAYS('2013-04-06 00:00:00')) COMMENT = '2013-04-05',
 		 PARTITION p20130406 VALUES LESS THAN (TO_DAYS('2013-04-07 00:00:00')) COMMENT = '2013-04-06',
 		 PARTITION p20130407 VALUES LESS THAN (TO_DAYS('2013-04-08 00:00:00')) COMMENT = '2013-04-07',
 		 PARTITION p20130408 VALUES LESS THAN (TO_DAYS('2013-04-09 00:00:00')) COMMENT = '2013-04-08',
 		 PARTITION p20130409 VALUES LESS THAN (TO_DAYS('2013-04-10 00:00:00')) COMMENT = '2013-04-09',
 		 PARTITION p20130410 VALUES LESS THAN (TO_DAYS('2013-04-11 00:00:00')) COMMENT = '2013-04-10',
 		 PARTITION p20130411 VALUES LESS THAN (TO_DAYS('2013-04-12 00:00:00')) COMMENT = '2013-04-11',
 		 PARTITION p20130412 VALUES LESS THAN (TO_DAYS('2013-04-13 00:00:00')) COMMENT = '2013-04-12',
 		 PARTITION p20130413 VALUES LESS THAN (TO_DAYS('2013-04-14 00:00:00')) COMMENT = '2013-04-13',
 		 PARTITION p20130414 VALUES LESS THAN (TO_DAYS('2013-04-15 00:00:00')) COMMENT = '2013-04-14',
 		 PARTITION p20130415 VALUES LESS THAN (TO_DAYS('2013-04-16 00:00:00')) COMMENT = '2013-04-15',
 		 PARTITION p20130416 VALUES LESS THAN (TO_DAYS('2013-04-17 00:00:00')) COMMENT = '2013-04-16',
 		 PARTITION p20130417 VALUES LESS THAN (TO_DAYS('2013-04-18 00:00:00')) COMMENT = '2013-04-17',
 		 PARTITION p20130418 VALUES LESS THAN (TO_DAYS('2013-04-19 00:00:00')) COMMENT = '2013-04-18'
);

4. パーティション追加

Addには理由があります。 パーティションの上限を指定しにくい場合に使われるMORE THAN VALUE は、パーティションを張り直すために REORGANISE PARTITIONが必要なためです。 この際、テーブルはLOCKがかかるので運用に支障がでます。 また、PARITIONは途中に差し込むことはできません。 一方で、上限を決めなければADD PARTITIONで後ろに追加が可能です。 こちらなら一瞬で追加されるので、運用上のメリットが高いのです。 run_AddPartition_sqlOutput.ps1
param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
)

function Get-PartitionSQLbyWeekday{

    [CmdletBinding()]
    param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
    )

    begin
    {
    }

    process
    {

        $output = @"
ALTER TABLE $table
    ADD PARTITION( `n
"@

        $output += $daysAdd `
            | %{$(Get-Date).AddDays($_)} `
            | %{"`t`t PARTITION p{3}{4:d2}{5:d2} VALUES LESS THAN (TO_DAYS('{0}-{1:d2}-{2:d2} 00:00:00')) COMMENT = '{3}-{4:d2}-{5:d2}',`n" `
                -f  $_.Year,
                    $_.Month,
                    $_.Day,
                    $_.AddDays("-1").Year,
                    $_.AddDays("-1").Month,
                    $_.AddDays("-1").Day}

        $beforeLast = $output.Length -2
        $output = $output.Substring(0,$beforeLast)
        $output += "`n"
        $output += "); "
    }

    end
    {
        $output
    }
}

Get-PartitionSQLbyWeekday -table $table -column $column -daysAdd $daysAdd `
    | Out-File -FilePath .\AddPartitionSQL_$table.sql -Encoding default
run_AddPartition_sqlOutput.bat バッチで実行してみます。 先ほど同様に、実行している本日からみて追加したい日を指定します。
powershell .\run_AddPartition_sqlOutput.ps1 -table "hoge" -column "created" -daysAdd (4..180)

pause
指定した日付に応じて、以下のSQLが生成されます。 run_AddPartition_sqlOutput.sql
ALTER TABLE hoge
    ADD PARTITION(
		 PARTITION p20130419 VALUES LESS THAN (TO_DAYS('2013-04-20 00:00:00')) COMMENT = '2013-04-19',
 		 PARTITION p20130420 VALUES LESS THAN (TO_DAYS('2013-04-21 00:00:00')) COMMENT = '2013-04-20',
 		 PARTITION p20130421 VALUES LESS THAN (TO_DAYS('2013-04-22 00:00:00')) COMMENT = '2013-04-21',
 		 PARTITION p20130422 VALUES LESS THAN (TO_DAYS('2013-04-23 00:00:00')) COMMENT = '2013-04-22',
 		 PARTITION p20130423 VALUES LESS THAN (TO_DAYS('2013-04-24 00:00:00')) COMMENT = '2013-04-23',
 		 PARTITION p20130424 VALUES LESS THAN (TO_DAYS('2013-04-25 00:00:00')) COMMENT = '2013-04-24',
 		 PARTITION p20130425 VALUES LESS THAN (TO_DAYS('2013-04-26 00:00:00')) COMMENT = '2013-04-25',
 		 PARTITION p20130426 VALUES LESS THAN (TO_DAYS('2013-04-27 00:00:00')) COMMENT = '2013-04-26',
 		 PARTITION p20130427 VALUES LESS THAN (TO_DAYS('2013-04-28 00:00:00')) COMMENT = '2013-04-27',
 		 PARTITION p20130428 VALUES LESS THAN (TO_DAYS('2013-04-29 00:00:00')) COMMENT = '2013-04-28',
 		 PARTITION p20130429 VALUES LESS THAN (TO_DAYS('2013-04-30 00:00:00')) COMMENT = '2013-04-29',
 		 PARTITION p20130430 VALUES LESS THAN (TO_DAYS('2013-05-01 00:00:00')) COMMENT = '2013-04-30',
 		 PARTITION p20130501 VALUES LESS THAN (TO_DAYS('2013-05-02 00:00:00')) COMMENT = '2013-05-01',
 		 PARTITION p20130502 VALUES LESS THAN (TO_DAYS('2013-05-03 00:00:00')) COMMENT = '2013-05-02',
 		 PARTITION p20130503 VALUES LESS THAN (TO_DAYS('2013-05-04 00:00:00')) COMMENT = '2013-05-03',
 		 PARTITION p20130504 VALUES LESS THAN (TO_DAYS('2013-05-05 00:00:00')) COMMENT = '2013-05-04',
 		 PARTITION p20130505 VALUES LESS THAN (TO_DAYS('2013-05-06 00:00:00')) COMMENT = '2013-05-05',
 		 PARTITION p20130506 VALUES LESS THAN (TO_DAYS('2013-05-07 00:00:00')) COMMENT = '2013-05-06',
 		 PARTITION p20130507 VALUES LESS THAN (TO_DAYS('2013-05-08 00:00:00')) COMMENT = '2013-05-07',
 		 PARTITION p20130508 VALUES LESS THAN (TO_DAYS('2013-05-09 00:00:00')) COMMENT = '2013-05-08',
 		 PARTITION p20130509 VALUES LESS THAN (TO_DAYS('2013-05-10 00:00:00')) COMMENT = '2013-05-09',
 		 PARTITION p20130510 VALUES LESS THAN (TO_DAYS('2013-05-11 00:00:00')) COMMENT = '2013-05-10',
 		 PARTITION p20130511 VALUES LESS THAN (TO_DAYS('2013-05-12 00:00:00')) COMMENT = '2013-05-11',
 		 PARTITION p20130512 VALUES LESS THAN (TO_DAYS('2013-05-13 00:00:00')) COMMENT = '2013-05-12',
 		 PARTITION p20130513 VALUES LESS THAN (TO_DAYS('2013-05-14 00:00:00')) COMMENT = '2013-05-13',
 		 PARTITION p20130514 VALUES LESS THAN (TO_DAYS('2013-05-15 00:00:00')) COMMENT = '2013-05-14',
 		 PARTITION p20130515 VALUES LESS THAN (TO_DAYS('2013-05-16 00:00:00')) COMMENT = '2013-05-15',
 		 PARTITION p20130516 VALUES LESS THAN (TO_DAYS('2013-05-17 00:00:00')) COMMENT = '2013-05-16',
 		 PARTITION p20130517 VALUES LESS THAN (TO_DAYS('2013-05-18 00:00:00')) COMMENT = '2013-05-17',
 		 PARTITION p20130518 VALUES LESS THAN (TO_DAYS('2013-05-19 00:00:00')) COMMENT = '2013-05-18',
 		 PARTITION p20130519 VALUES LESS THAN (TO_DAYS('2013-05-20 00:00:00')) COMMENT = '2013-05-19',
 		 PARTITION p20130520 VALUES LESS THAN (TO_DAYS('2013-05-21 00:00:00')) COMMENT = '2013-05-20',
 		 PARTITION p20130521 VALUES LESS THAN (TO_DAYS('2013-05-22 00:00:00')) COMMENT = '2013-05-21',
 		 PARTITION p20130522 VALUES LESS THAN (TO_DAYS('2013-05-23 00:00:00')) COMMENT = '2013-05-22',
 		 PARTITION p20130523 VALUES LESS THAN (TO_DAYS('2013-05-24 00:00:00')) COMMENT = '2013-05-23',
 		 PARTITION p20130524 VALUES LESS THAN (TO_DAYS('2013-05-25 00:00:00')) COMMENT = '2013-05-24',
 		 PARTITION p20130525 VALUES LESS THAN (TO_DAYS('2013-05-26 00:00:00')) COMMENT = '2013-05-25',
 		 PARTITION p20130526 VALUES LESS THAN (TO_DAYS('2013-05-27 00:00:00')) COMMENT = '2013-05-26',
 		 PARTITION p20130527 VALUES LESS THAN (TO_DAYS('2013-05-28 00:00:00')) COMMENT = '2013-05-27',
 		 PARTITION p20130528 VALUES LESS THAN (TO_DAYS('2013-05-29 00:00:00')) COMMENT = '2013-05-28',
 		 PARTITION p20130529 VALUES LESS THAN (TO_DAYS('2013-05-30 00:00:00')) COMMENT = '2013-05-29',
 		 PARTITION p20130530 VALUES LESS THAN (TO_DAYS('2013-05-31 00:00:00')) COMMENT = '2013-05-30',
 		 PARTITION p20130531 VALUES LESS THAN (TO_DAYS('2013-06-01 00:00:00')) COMMENT = '2013-05-31',
 		 PARTITION p20130601 VALUES LESS THAN (TO_DAYS('2013-06-02 00:00:00')) COMMENT = '2013-06-01',
 		 PARTITION p20130602 VALUES LESS THAN (TO_DAYS('2013-06-03 00:00:00')) COMMENT = '2013-06-02',
 		 PARTITION p20130603 VALUES LESS THAN (TO_DAYS('2013-06-04 00:00:00')) COMMENT = '2013-06-03',
 		 PARTITION p20130604 VALUES LESS THAN (TO_DAYS('2013-06-05 00:00:00')) COMMENT = '2013-06-04',
 		 PARTITION p20130605 VALUES LESS THAN (TO_DAYS('2013-06-06 00:00:00')) COMMENT = '2013-06-05',
 		 PARTITION p20130606 VALUES LESS THAN (TO_DAYS('2013-06-07 00:00:00')) COMMENT = '2013-06-06',
 		 PARTITION p20130607 VALUES LESS THAN (TO_DAYS('2013-06-08 00:00:00')) COMMENT = '2013-06-07',
 		 PARTITION p20130608 VALUES LESS THAN (TO_DAYS('2013-06-09 00:00:00')) COMMENT = '2013-06-08',
 		 PARTITION p20130609 VALUES LESS THAN (TO_DAYS('2013-06-10 00:00:00')) COMMENT = '2013-06-09',
 		 PARTITION p20130610 VALUES LESS THAN (TO_DAYS('2013-06-11 00:00:00')) COMMENT = '2013-06-10',
 		 PARTITION p20130611 VALUES LESS THAN (TO_DAYS('2013-06-12 00:00:00')) COMMENT = '2013-06-11',
 		 PARTITION p20130612 VALUES LESS THAN (TO_DAYS('2013-06-13 00:00:00')) COMMENT = '2013-06-12',
 		 PARTITION p20130613 VALUES LESS THAN (TO_DAYS('2013-06-14 00:00:00')) COMMENT = '2013-06-13',
 		 PARTITION p20130614 VALUES LESS THAN (TO_DAYS('2013-06-15 00:00:00')) COMMENT = '2013-06-14',
 		 PARTITION p20130615 VALUES LESS THAN (TO_DAYS('2013-06-16 00:00:00')) COMMENT = '2013-06-15',
 		 PARTITION p20130616 VALUES LESS THAN (TO_DAYS('2013-06-17 00:00:00')) COMMENT = '2013-06-16',
 		 PARTITION p20130617 VALUES LESS THAN (TO_DAYS('2013-06-18 00:00:00')) COMMENT = '2013-06-17',
 		 PARTITION p20130618 VALUES LESS THAN (TO_DAYS('2013-06-19 00:00:00')) COMMENT = '2013-06-18',
 		 PARTITION p20130619 VALUES LESS THAN (TO_DAYS('2013-06-20 00:00:00')) COMMENT = '2013-06-19',
 		 PARTITION p20130620 VALUES LESS THAN (TO_DAYS('2013-06-21 00:00:00')) COMMENT = '2013-06-20',
 		 PARTITION p20130621 VALUES LESS THAN (TO_DAYS('2013-06-22 00:00:00')) COMMENT = '2013-06-21',
 		 PARTITION p20130622 VALUES LESS THAN (TO_DAYS('2013-06-23 00:00:00')) COMMENT = '2013-06-22',
 		 PARTITION p20130623 VALUES LESS THAN (TO_DAYS('2013-06-24 00:00:00')) COMMENT = '2013-06-23',
 		 PARTITION p20130624 VALUES LESS THAN (TO_DAYS('2013-06-25 00:00:00')) COMMENT = '2013-06-24',
 		 PARTITION p20130625 VALUES LESS THAN (TO_DAYS('2013-06-26 00:00:00')) COMMENT = '2013-06-25',
 		 PARTITION p20130626 VALUES LESS THAN (TO_DAYS('2013-06-27 00:00:00')) COMMENT = '2013-06-26',
 		 PARTITION p20130627 VALUES LESS THAN (TO_DAYS('2013-06-28 00:00:00')) COMMENT = '2013-06-27',
 		 PARTITION p20130628 VALUES LESS THAN (TO_DAYS('2013-06-29 00:00:00')) COMMENT = '2013-06-28',
 		 PARTITION p20130629 VALUES LESS THAN (TO_DAYS('2013-06-30 00:00:00')) COMMENT = '2013-06-29',
 		 PARTITION p20130630 VALUES LESS THAN (TO_DAYS('2013-07-01 00:00:00')) COMMENT = '2013-06-30',
 		 PARTITION p20130701 VALUES LESS THAN (TO_DAYS('2013-07-02 00:00:00')) COMMENT = '2013-07-01',
 		 PARTITION p20130702 VALUES LESS THAN (TO_DAYS('2013-07-03 00:00:00')) COMMENT = '2013-07-02',
 		 PARTITION p20130703 VALUES LESS THAN (TO_DAYS('2013-07-04 00:00:00')) COMMENT = '2013-07-03',
 		 PARTITION p20130704 VALUES LESS THAN (TO_DAYS('2013-07-05 00:00:00')) COMMENT = '2013-07-04',
 		 PARTITION p20130705 VALUES LESS THAN (TO_DAYS('2013-07-06 00:00:00')) COMMENT = '2013-07-05',
 		 PARTITION p20130706 VALUES LESS THAN (TO_DAYS('2013-07-07 00:00:00')) COMMENT = '2013-07-06',
 		 PARTITION p20130707 VALUES LESS THAN (TO_DAYS('2013-07-08 00:00:00')) COMMENT = '2013-07-07',
 		 PARTITION p20130708 VALUES LESS THAN (TO_DAYS('2013-07-09 00:00:00')) COMMENT = '2013-07-08',
 		 PARTITION p20130709 VALUES LESS THAN (TO_DAYS('2013-07-10 00:00:00')) COMMENT = '2013-07-09',
 		 PARTITION p20130710 VALUES LESS THAN (TO_DAYS('2013-07-11 00:00:00')) COMMENT = '2013-07-10',
 		 PARTITION p20130711 VALUES LESS THAN (TO_DAYS('2013-07-12 00:00:00')) COMMENT = '2013-07-11',
 		 PARTITION p20130712 VALUES LESS THAN (TO_DAYS('2013-07-13 00:00:00')) COMMENT = '2013-07-12',
 		 PARTITION p20130713 VALUES LESS THAN (TO_DAYS('2013-07-14 00:00:00')) COMMENT = '2013-07-13',
 		 PARTITION p20130714 VALUES LESS THAN (TO_DAYS('2013-07-15 00:00:00')) COMMENT = '2013-07-14',
 		 PARTITION p20130715 VALUES LESS THAN (TO_DAYS('2013-07-16 00:00:00')) COMMENT = '2013-07-15',
 		 PARTITION p20130716 VALUES LESS THAN (TO_DAYS('2013-07-17 00:00:00')) COMMENT = '2013-07-16',
 		 PARTITION p20130717 VALUES LESS THAN (TO_DAYS('2013-07-18 00:00:00')) COMMENT = '2013-07-17',
 		 PARTITION p20130718 VALUES LESS THAN (TO_DAYS('2013-07-19 00:00:00')) COMMENT = '2013-07-18',
 		 PARTITION p20130719 VALUES LESS THAN (TO_DAYS('2013-07-20 00:00:00')) COMMENT = '2013-07-19',
 		 PARTITION p20130720 VALUES LESS THAN (TO_DAYS('2013-07-21 00:00:00')) COMMENT = '2013-07-20',
 		 PARTITION p20130721 VALUES LESS THAN (TO_DAYS('2013-07-22 00:00:00')) COMMENT = '2013-07-21',
 		 PARTITION p20130722 VALUES LESS THAN (TO_DAYS('2013-07-23 00:00:00')) COMMENT = '2013-07-22',
 		 PARTITION p20130723 VALUES LESS THAN (TO_DAYS('2013-07-24 00:00:00')) COMMENT = '2013-07-23',
 		 PARTITION p20130724 VALUES LESS THAN (TO_DAYS('2013-07-25 00:00:00')) COMMENT = '2013-07-24',
 		 PARTITION p20130725 VALUES LESS THAN (TO_DAYS('2013-07-26 00:00:00')) COMMENT = '2013-07-25',
 		 PARTITION p20130726 VALUES LESS THAN (TO_DAYS('2013-07-27 00:00:00')) COMMENT = '2013-07-26',
 		 PARTITION p20130727 VALUES LESS THAN (TO_DAYS('2013-07-28 00:00:00')) COMMENT = '2013-07-27',
 		 PARTITION p20130728 VALUES LESS THAN (TO_DAYS('2013-07-29 00:00:00')) COMMENT = '2013-07-28',
 		 PARTITION p20130729 VALUES LESS THAN (TO_DAYS('2013-07-30 00:00:00')) COMMENT = '2013-07-29',
 		 PARTITION p20130730 VALUES LESS THAN (TO_DAYS('2013-07-31 00:00:00')) COMMENT = '2013-07-30',
 		 PARTITION p20130731 VALUES LESS THAN (TO_DAYS('2013-08-01 00:00:00')) COMMENT = '2013-07-31',
 		 PARTITION p20130801 VALUES LESS THAN (TO_DAYS('2013-08-02 00:00:00')) COMMENT = '2013-08-01',
 		 PARTITION p20130802 VALUES LESS THAN (TO_DAYS('2013-08-03 00:00:00')) COMMENT = '2013-08-02',
 		 PARTITION p20130803 VALUES LESS THAN (TO_DAYS('2013-08-04 00:00:00')) COMMENT = '2013-08-03',
 		 PARTITION p20130804 VALUES LESS THAN (TO_DAYS('2013-08-05 00:00:00')) COMMENT = '2013-08-04',
 		 PARTITION p20130805 VALUES LESS THAN (TO_DAYS('2013-08-06 00:00:00')) COMMENT = '2013-08-05',
 		 PARTITION p20130806 VALUES LESS THAN (TO_DAYS('2013-08-07 00:00:00')) COMMENT = '2013-08-06',
 		 PARTITION p20130807 VALUES LESS THAN (TO_DAYS('2013-08-08 00:00:00')) COMMENT = '2013-08-07',
 		 PARTITION p20130808 VALUES LESS THAN (TO_DAYS('2013-08-09 00:00:00')) COMMENT = '2013-08-08',
 		 PARTITION p20130809 VALUES LESS THAN (TO_DAYS('2013-08-10 00:00:00')) COMMENT = '2013-08-09',
 		 PARTITION p20130810 VALUES LESS THAN (TO_DAYS('2013-08-11 00:00:00')) COMMENT = '2013-08-10',
 		 PARTITION p20130811 VALUES LESS THAN (TO_DAYS('2013-08-12 00:00:00')) COMMENT = '2013-08-11',
 		 PARTITION p20130812 VALUES LESS THAN (TO_DAYS('2013-08-13 00:00:00')) COMMENT = '2013-08-12',
 		 PARTITION p20130813 VALUES LESS THAN (TO_DAYS('2013-08-14 00:00:00')) COMMENT = '2013-08-13',
 		 PARTITION p20130814 VALUES LESS THAN (TO_DAYS('2013-08-15 00:00:00')) COMMENT = '2013-08-14',
 		 PARTITION p20130815 VALUES LESS THAN (TO_DAYS('2013-08-16 00:00:00')) COMMENT = '2013-08-15',
 		 PARTITION p20130816 VALUES LESS THAN (TO_DAYS('2013-08-17 00:00:00')) COMMENT = '2013-08-16',
 		 PARTITION p20130817 VALUES LESS THAN (TO_DAYS('2013-08-18 00:00:00')) COMMENT = '2013-08-17',
 		 PARTITION p20130818 VALUES LESS THAN (TO_DAYS('2013-08-19 00:00:00')) COMMENT = '2013-08-18',
 		 PARTITION p20130819 VALUES LESS THAN (TO_DAYS('2013-08-20 00:00:00')) COMMENT = '2013-08-19',
 		 PARTITION p20130820 VALUES LESS THAN (TO_DAYS('2013-08-21 00:00:00')) COMMENT = '2013-08-20',
 		 PARTITION p20130821 VALUES LESS THAN (TO_DAYS('2013-08-22 00:00:00')) COMMENT = '2013-08-21',
 		 PARTITION p20130822 VALUES LESS THAN (TO_DAYS('2013-08-23 00:00:00')) COMMENT = '2013-08-22',
 		 PARTITION p20130823 VALUES LESS THAN (TO_DAYS('2013-08-24 00:00:00')) COMMENT = '2013-08-23',
 		 PARTITION p20130824 VALUES LESS THAN (TO_DAYS('2013-08-25 00:00:00')) COMMENT = '2013-08-24',
 		 PARTITION p20130825 VALUES LESS THAN (TO_DAYS('2013-08-26 00:00:00')) COMMENT = '2013-08-25',
 		 PARTITION p20130826 VALUES LESS THAN (TO_DAYS('2013-08-27 00:00:00')) COMMENT = '2013-08-26',
 		 PARTITION p20130827 VALUES LESS THAN (TO_DAYS('2013-08-28 00:00:00')) COMMENT = '2013-08-27',
 		 PARTITION p20130828 VALUES LESS THAN (TO_DAYS('2013-08-29 00:00:00')) COMMENT = '2013-08-28',
 		 PARTITION p20130829 VALUES LESS THAN (TO_DAYS('2013-08-30 00:00:00')) COMMENT = '2013-08-29',
 		 PARTITION p20130830 VALUES LESS THAN (TO_DAYS('2013-08-31 00:00:00')) COMMENT = '2013-08-30',
 		 PARTITION p20130831 VALUES LESS THAN (TO_DAYS('2013-09-01 00:00:00')) COMMENT = '2013-08-31',
 		 PARTITION p20130901 VALUES LESS THAN (TO_DAYS('2013-09-02 00:00:00')) COMMENT = '2013-09-01',
 		 PARTITION p20130902 VALUES LESS THAN (TO_DAYS('2013-09-03 00:00:00')) COMMENT = '2013-09-02',
 		 PARTITION p20130903 VALUES LESS THAN (TO_DAYS('2013-09-04 00:00:00')) COMMENT = '2013-09-03',
 		 PARTITION p20130904 VALUES LESS THAN (TO_DAYS('2013-09-05 00:00:00')) COMMENT = '2013-09-04',
 		 PARTITION p20130905 VALUES LESS THAN (TO_DAYS('2013-09-06 00:00:00')) COMMENT = '2013-09-05',
 		 PARTITION p20130906 VALUES LESS THAN (TO_DAYS('2013-09-07 00:00:00')) COMMENT = '2013-09-06',
 		 PARTITION p20130907 VALUES LESS THAN (TO_DAYS('2013-09-08 00:00:00')) COMMENT = '2013-09-07',
 		 PARTITION p20130908 VALUES LESS THAN (TO_DAYS('2013-09-09 00:00:00')) COMMENT = '2013-09-08',
 		 PARTITION p20130909 VALUES LESS THAN (TO_DAYS('2013-09-10 00:00:00')) COMMENT = '2013-09-09',
 		 PARTITION p20130910 VALUES LESS THAN (TO_DAYS('2013-09-11 00:00:00')) COMMENT = '2013-09-10',
 		 PARTITION p20130911 VALUES LESS THAN (TO_DAYS('2013-09-12 00:00:00')) COMMENT = '2013-09-11',
 		 PARTITION p20130912 VALUES LESS THAN (TO_DAYS('2013-09-13 00:00:00')) COMMENT = '2013-09-12',
 		 PARTITION p20130913 VALUES LESS THAN (TO_DAYS('2013-09-14 00:00:00')) COMMENT = '2013-09-13',
 		 PARTITION p20130914 VALUES LESS THAN (TO_DAYS('2013-09-15 00:00:00')) COMMENT = '2013-09-14',
 		 PARTITION p20130915 VALUES LESS THAN (TO_DAYS('2013-09-16 00:00:00')) COMMENT = '2013-09-15',
 		 PARTITION p20130916 VALUES LESS THAN (TO_DAYS('2013-09-17 00:00:00')) COMMENT = '2013-09-16',
 		 PARTITION p20130917 VALUES LESS THAN (TO_DAYS('2013-09-18 00:00:00')) COMMENT = '2013-09-17',
 		 PARTITION p20130918 VALUES LESS THAN (TO_DAYS('2013-09-19 00:00:00')) COMMENT = '2013-09-18',
 		 PARTITION p20130919 VALUES LESS THAN (TO_DAYS('2013-09-20 00:00:00')) COMMENT = '2013-09-19',
 		 PARTITION p20130920 VALUES LESS THAN (TO_DAYS('2013-09-21 00:00:00')) COMMENT = '2013-09-20',
 		 PARTITION p20130921 VALUES LESS THAN (TO_DAYS('2013-09-22 00:00:00')) COMMENT = '2013-09-21',
 		 PARTITION p20130922 VALUES LESS THAN (TO_DAYS('2013-09-23 00:00:00')) COMMENT = '2013-09-22',
 		 PARTITION p20130923 VALUES LESS THAN (TO_DAYS('2013-09-24 00:00:00')) COMMENT = '2013-09-23',
 		 PARTITION p20130924 VALUES LESS THAN (TO_DAYS('2013-09-25 00:00:00')) COMMENT = '2013-09-24',
 		 PARTITION p20130925 VALUES LESS THAN (TO_DAYS('2013-09-26 00:00:00')) COMMENT = '2013-09-25',
 		 PARTITION p20130926 VALUES LESS THAN (TO_DAYS('2013-09-27 00:00:00')) COMMENT = '2013-09-26',
 		 PARTITION p20130927 VALUES LESS THAN (TO_DAYS('2013-09-28 00:00:00')) COMMENT = '2013-09-27',
 		 PARTITION p20130928 VALUES LESS THAN (TO_DAYS('2013-09-29 00:00:00')) COMMENT = '2013-09-28',
 		 PARTITION p20130929 VALUES LESS THAN (TO_DAYS('2013-09-30 00:00:00')) COMMENT = '2013-09-29',
 		 PARTITION p20130930 VALUES LESS THAN (TO_DAYS('2013-10-01 00:00:00')) COMMENT = '2013-09-30',
 		 PARTITION p20131001 VALUES LESS THAN (TO_DAYS('2013-10-02 00:00:00')) COMMENT = '2013-10-01',
 		 PARTITION p20131002 VALUES LESS THAN (TO_DAYS('2013-10-03 00:00:00')) COMMENT = '2013-10-02',
 		 PARTITION p20131003 VALUES LESS THAN (TO_DAYS('2013-10-04 00:00:00')) COMMENT = '2013-10-03',
 		 PARTITION p20131004 VALUES LESS THAN (TO_DAYS('2013-10-05 00:00:00')) COMMENT = '2013-10-04',
 		 PARTITION p20131005 VALUES LESS THAN (TO_DAYS('2013-10-06 00:00:00')) COMMENT = '2013-10-05',
 		 PARTITION p20131006 VALUES LESS THAN (TO_DAYS('2013-10-07 00:00:00')) COMMENT = '2013-10-06',
 		 PARTITION p20131007 VALUES LESS THAN (TO_DAYS('2013-10-08 00:00:00')) COMMENT = '2013-10-07',
 		 PARTITION p20131008 VALUES LESS THAN (TO_DAYS('2013-10-09 00:00:00')) COMMENT = '2013-10-08',
 		 PARTITION p20131009 VALUES LESS THAN (TO_DAYS('2013-10-10 00:00:00')) COMMENT = '2013-10-09',
 		 PARTITION p20131010 VALUES LESS THAN (TO_DAYS('2013-10-11 00:00:00')) COMMENT = '2013-10-10',
 		 PARTITION p20131011 VALUES LESS THAN (TO_DAYS('2013-10-12 00:00:00')) COMMENT = '2013-10-11',
 		 PARTITION p20131012 VALUES LESS THAN (TO_DAYS('2013-10-13 00:00:00')) COMMENT = '2013-10-12'
);

5. パーティションドロップ

パーティションのメリットの一つが、パーティションのドロップによるデータの削除高速化ですね。 ということで、Drop Partition構文でしす。 run_DropPartition_sqlOutput.ps1
param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
)

function Get-PartitionSQLbyWeekday{

    [CmdletBinding()]
    param(
    [parameter(Mandatory=$true)]
    [string]$table,
    [parameter(Mandatory=$true)]
    [string]$column,
    [parameter(Mandatory=$true)]
    [int[]]$daysAdd
    )

    begin
    {
    }

    process
    {

        $output = @"
ALTER TABLE $table
    DROP PARTITION `n
"@

        $output += $daysAdd `
            | %{$(Get-Date).AddDays($_)} `
            | %{"`t`t p{0}{1:d2}{2:d2},`n" -f $_.Year,$_.Month,$_.Day,$_.AddDays("-1").Day}

        $beforeLast = $output.Length -2
        $output = $output.Substring(0,$beforeLast)
        $output += "`n"
        $output += "; "
    }

    end
    {
        $output
    }
}



Get-PartitionSQLbyWeekday -table $table -column $column -daysAdd $daysAdd `
    | Out-File -FilePath .\DropPartitionSQL_$table.sql -Encoding default
run_DropPartition_sqlOutput.bat バッチで実行してみます。 先ほど同様に、実行している本日からみて追加したい日を指定します。
powershell .\run_DropPartition_sqlOutput.ps1 -table "hoge" -column "created" -daysAdd (-82..-32)

pause
指定した日付に応じて、以下のSQLが生成されます。 run_AddPartition_sqlOutput.sql
ALTER TABLE hoge
    DROP PARTITION
		 p20130124,
 		 p20130125,
 		 p20130126,
 		 p20130127,
 		 p20130128,
 		 p20130129,
 		 p20130130,
 		 p20130131,
 		 p20130201,
 		 p20130202,
 		 p20130203,
 		 p20130204,
 		 p20130205,
 		 p20130206,
 		 p20130207,
 		 p20130208,
 		 p20130209,
 		 p20130210,
 		 p20130211,
 		 p20130212,
 		 p20130213,
 		 p20130214,
 		 p20130215,
 		 p20130216,
 		 p20130217,
 		 p20130218,
 		 p20130219,
 		 p20130220,
 		 p20130221,
 		 p20130222,
 		 p20130223,
 		 p20130224,
 		 p20130225,
 		 p20130226,
 		 p20130227,
 		 p20130228,
 		 p20130301,
 		 p20130302,
 		 p20130303,
 		 p20130304,
 		 p20130305,
 		 p20130306,
 		 p20130307,
 		 p20130308,
 		 p20130309,
 		 p20130310,
 		 p20130311,
 		 p20130312,
 		 p20130313,
 		 p20130314,
 		 p20130315
;

最後に

バッチで呼び出すのでもいいとは思うのです。 要は使いやすければ。 DRYのために、一つのバッチから変数で渡せるといいですね。 生SQLとか書きますが、こういう事例では積極的に撲滅します。