当前位置：首页 > 数据库 > MSSQL教程 > 创建分区的SP和job > 正文

创建分区的SP和job

来源：这里教程网 时间：2026-03-02 11:10:06 作者：
    创建SP

点击(此处)折叠或打开

    create procedure sp_maintain_partion_fg (
         @tableName varchar(50),
     @inputdate datetime
    )
     as begin
     declare
     @fileGroupName varchar(50),
     @ndfName varchar(50),
    @newNameStr varchar(50),
    @fullPath varchar(50),
    @newDay varchar(50),
    @oldDay datetime,
    @partFunName varchar(50),
    @schemeName varchar(50),
     @sqlstr varchar(1000),
     @sql1 varchar(4000)

    --set @tableName='DYDB'
     set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0), 23 )--CONVERT(varchar(100), @inputdate, 23)--23:按天 114:按时间
     set @oldDay=cast(CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate)-1, 0), 112 ) as datetime)
     set @newNameStr=left(Replace(Replace(@newDay,':','_'),'-','_'),7)
     set @fileGroupName=N'G'+@newNameStr
     set @ndfName=N'F'+@newNameStr+''
     set @fullPath=N'F:\\SQLData\\ecodata\\'+@ndfName+'.ndf'
     set @partFunName=N'pf_Time'
     set @schemeName=N'ps_Time'

    --print @fullPath
     --print @fileGroupName
     --print @ndfName



    --创建文件组
     if exists(select * from sys.filegroups where name=@fileGroupName)
     begin
     print '文件组存在,不需添加'
     end
     else
     begin
     exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
     --print 'exec '+('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
     print '新增文件组'
     if exists(select * from sys.partition_schemes where name =@schemeName)
     begin
     exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
     --print 'exec '+('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
     print '修改分区方案'
     end

    print 'exec '+('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
     print '修改分区方案'

    if exists(select * from sys.partition_range_values where function_id=(select function_id from
    sys.partition_functions where name =@partFunName) and value=@oldDay)
     begin
     exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
     --print 'exec '+('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
     print '修改分区函数'
     end
     end

    --创建NDF文件
     if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
     begin
     print 'ndf文件存在,不需添加'
     end
     else
     begin
     exec('ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
     print 'ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'

    print '新创建ndf文件'
     end
     --/*--------------------以上创建数据库的文件组和物理文件------------------------*/
     end



    ----分区函数
     --if exists(select * from sys.partition_functions where name =@partFunName)
     --begin
     --print '此处修改需要在修改分区函数之前执行'
     --end
     --else
     --begin
     --exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')')
     ----print 'CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')'
     --print '新创建分区函数'
     --end
     ----分区方案
     --if exists(select * from sys.partition_schemes where name =@schemeName)
     --begin
     --print '此处修改需要在修改分区方案之前执行'
     --end
     --else
     --begin
     --exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
     ----print ('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
     --print '新创建分区方案'
2. 增加job

点击(此处)折叠或打开

    declare @date date
         set @date= DATEADD(mm,1,getdate())
     print @date
     exec sp_maintain_partion_fg 'ecodata',@date

编辑推荐：

相关推荐

最新软件资讯

sql server 2012官方手册之一
sql server 2012官方手册之一

热文推荐

天极热推