自动生成数据库表分区脚本

--按时间（日期）动态生成SQL SERVER数据库分区脚本，分好区后，手动和表建立关联即可--生成分区脚本DECLARE @DataBaseName NVARCHAR(50)--数据库名称DECLARE @TableName NVARCHAR(50)--表名称DECLARE @ColumnName NVARCHAR(50)--字段名称DECLARE @PartNumber INT--需要分多少个区DECLARE @Location NVARCHAR(50)--保存分区文件的路径DECLARE @Size NVARCHAR(50)--分区初始化大小DECLARE @FileGrowth NVARCHAR(50)--分区文件增量--DECLARE @FunValue INT--分区分段值DECLARE @i INTDECLARE @y INT      --起始年份DECLARE @m INT      --起始月份DECLARE @d INT      --起始天DECLARE @dayGap INT --分区分段值 天数DECLARE @PartNumberStr NVARCHAR(50)DECLARE @sql NVARCHAR(max)DECLARE @ValueStart NVARCHAR(50)--设置下面变量SET @DataBaseName = 'LOREAL_MSCRM'SET @TableName = 'tsr_transactionBase'SET @ColumnName = 'tsr_transactiondate'SET @PartNumber = 150SET @Location = 'F:\Data\'SET @Size = '80MB'SET @FileGrowth = '30%'--2007-03-02 13:51:06.147SET @y = 2007SET @m = 3SET @d = 2SET @dayGap = 30--1.创建文件组SET @i = 1PRINT '--1.创建文件组'WHILE @i <= @PartNumberBEGIN    SET @PartNumberStr =  RIGHT('000' + CONVERT(NVARCHAR,@i),4)    SET @sql = 'ALTER DATABASE ['+@DataBaseName +']ADD FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']'    PRINT @sql + CHAR(13)    SET @i=@i+1END--2.创建文件SET @i = 1PRINT CHAR(13)+'--2.创建文件'WHILE @i <= @PartNumberBEGIN    SET @PartNumberStr =  RIGHT('000' + CONVERT(NVARCHAR,@i),4)    SET @sql = 'ALTER DATABASE ['+@DataBaseName +']ADD FILE(NAME = N''FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data'',FILENAME = N'''+@Location+'FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data.ndf'',SIZE = '+@Size+', FILEGROWTH = '+@FileGrowth+' )TO FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];'    PRINT @sql + CHAR(13)    SET @i=@i+1END--3.创建分区函数PRINT CHAR(13)+'--3.创建分区函数'DECLARE @FunValueStr NVARCHAR(MAX) --SET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1)SET @sql = 'CREATE PARTITION FUNCTIONFun_'+@TableName+'_'+@ColumnName+'(DATETIME) ASRANGE RIGHTFOR VALUES('PRINT @sqlSET @i = 1SET @FunValueStr = ''WHILE @i < @PartNumberBEGIN    SET @FunValueStr = @FunValueStr + '''' + RIGHT(CONVERT(NVARCHAR, @y),4) + '-' + RIGHT('0' + CONVERT(NVARCHAR, @m),2) + '-' + RIGHT('0' + CONVERT(NVARCHAR, @d),2) + ' 00:00:00.000'','    SET @d = @d + @dayGapIF (@d > 30 OR (@m = 2 AND @d > 28))  --二月需要特殊处理一下        BEGIN            SET @d = 1            SET @m = @m + 1            if (@m > 12)                BEGIN                    SET @m = 1                    SET @y = @y + 1                END        END    IF (@i = @PartNumber - 1)BEGINSET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1)END    PRINT @FunValueStr    SET @FunValueStr = ''    SET @i=@i+1ENDPRINT ')' + CHAR(13)--4.创建分区方案PRINT CHAR(13)+'--4.创建分区方案'DECLARE @FileGroupStr NVARCHAR(MAX) --SET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1)SET @sql = 'CREATE PARTITION SCHEMESch_'+@TableName+'_'+@ColumnName+' ASPARTITION Fun_'+@TableName+'_'+@ColumnName+'TO('PRINT @sqlSET @i = 1SET @FileGroupStr = ''WHILE @i <= @PartNumberBEGIN    SET @PartNumberStr =  RIGHT('000' + CONVERT(NVARCHAR,@i),4)    SET @FileGroupStr = @FileGroupStr + '[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],'        IF (@i = @PartNumber)BEGINSET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1)END    PRINT @FileGroupStr    SET @FileGroupStr = ''        SET @i=@i+1ENDPRINT ')'--5.分区函数的记录数PRINT CHAR(13)+'--5.分区函数的记录数'SET @sql = 'SELECT $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+') AS Partition_num,MIN('+@ColumnName+') AS Min_value,MAX('+@ColumnName+') AS Max_value,COUNT(1) AS Record_numFROM dbo.'+@TableName+'GROUP BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+')ORDER BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+');'PRINT @sql + CHAR(13)