How to create SP and job for partitions in a database
Editor to share with you how to create partition SP and job in the database. I hope you will get something after reading this article. Let's discuss it together.
Create SP
Click (here) to collapse or open
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: by day
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
-- create filegroup
If exists (select * from sys.filegroups where name=@fileGroupName)
Begin
Print 'filegroup exists, no need to add'
End
Else
Begin
Exec ('ALTER DATABASE' + @ tableName+' ADD FILEGROUP ['+ @ fileGroupName+']')
-- print 'exec' + ('ALTER DATABASE' + @ tableName+' ADD FILEGROUP ['+ @ fileGroupName+']')
Print 'add filegroup'
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 'modify Partition Scheme'
End
Print 'exec' + ('alter partition scheme' + @ schemeName+' next used ['+ @ fileGroupName+']')
Print 'modify Partition Scheme'
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 'modify partition function'
End
End
-- create NDF file
If exists (select * from sys.database_files where [state] = 0 and (name=@ndfName or physical_name=@fullPath))
Begin
Print 'ndf file exists, no need to add'
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 'newly created ndf File'
End
-- / *-the above filegroups and physical files that create the database-* /
End
-Partition function
-- if exists (select * from sys.partition_functions where name = @ partFunName)
-- begin
-- print 'modify here needs to be performed before modifying the partition function'
-- 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 'newly created partition function'
-- end
-Partition scheme
-- if exists (select * from sys.partition_schemes where name = @ schemeName)
-- begin
-- print 'changes here need to be performed before modifying the partition scheme'
-- 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 'newly created Partition Scheme'
two。 Add job
Click (here) to collapse or open
Declare @ date date
Set @ date= DATEADD (mm,1,getdate ())
Print @ date
Exec sp_maintain_partion_fg 'ecodata',@date
After reading this article, I believe you have a certain understanding of "how to create partition SP and job in the database". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!