DECLARE
@fileGroupName VARCHAR(20), --文件组名(格式为:FG+@Month)
@fileName VARCHAR(20), --文件名(格式为:F+@Month)
@filePath VARCHAR(100), --文件存放路径(格式为:存放目录路径+@fileName.ndf)
@dataBaseName VARCHAR(20), --数据库名
@Month VARCHAR(10), --当前时间年月(格式为:yyyymm)
@schemeName VARCHAR(20), --分区方案名
@partFunctionName VARCHAR(20), --分区函数名
@limit VARCHAR(10) --分区界限(以时间分区则为时间字符串,格式为:mm/dd/yyyy)
SET @fileGroupName='FG201805'
SET @Month=CONVERT(varchar(10),GETDATE(),112)
SET @fileName=N'F201805'
SET @filePath='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\F201805.ndf'
SET @dataBaseName='Chassis'
SET @schemeName='Scheme_DateTime'
SET @partFunctionName='Function_DateTime'
SET @limit=CONVERT(varchar(10),GETDATE(),101)
--语句要指明需要操作的数据库
if exists(select * from Chassis.sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@dataBaseName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'+@fileGroupName
end
if exists(select * from Chassis.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@dataBaseName+' ADD FILE(NAME ='''+@fileName+''',FILENAME = '''+@filePath+''')TO FILEGROUP ['+@fileGroupName+']')
print '添加文件'+@fileName+'至文件组'+@fileGroupName
end
if exists(select * from sys.partition_schemes where name=@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区方案,指定下一分区的文件组'
end
else
begin
print '分区方案不存在'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_functions where name=@partFunctionName))
begin
if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name='Scheme_DateTime') and value=CONVERT(datetime,''+@limit+'',101))
begin
print '界限已存在'
end
else
begin
exec('alter partition function '+@partFunctionName+'() split range('''+@limit+''')')
print '修改分区函数,添加划分界限为:'+@limit
end
end
else
begin
print '分区函数不存在'
end