SQlserver分区表函数.doc

1，建立分区表脚本要手工录入，表名，分区函数字段类型，分区函数的分区方式，分区方案的文件组，新建表准备要分区的字段，新建表脚本。2，输入表名 查询分区函数 的函数3，输入表名 查询分区方案 的函数4，数据表名 查询分区range 的脚本5，输入表名，查询分区range的表值函数6，查询数据库分区表，查询语句7，增加分区表存储过程8，合并分区表存储过程9，统计分区表各分区的数据量10，判断表是否为分区表的函数，返回（1，0）值1， 建立分区表脚本 （这个要输入的内容太多）use masterexec sp_configure show advanced options, 1RECONFIGUREGOEXEC sp_configure xp_cmdshell, 1GORECONFIGUREGOuse larry-初始化设置declare table_name varchar(50)declare p_field varchar(50)declare p_range varchar(50)declare p_scheme_filegroup varchar(50)declare table_filed varchar(50)declare create_table varchar(200)declare create_p_fun varchar(200)declare create_p_scheme varchar(200)declare create_p_table varchar(200)set table_name=larry -输入要建立分区表表名set p_field=datetime -输入表字段类型如:datetimeset p_range=2011-01-01,2012-01-01 -输入分区函数的分区方式如: 2011-01-01,2012-01-01set p_scheme_filegroup=larry1,larry2,larry3 -输入分区方案的文件组 如: larry1,larry2,larry3set table_filed=addtime -输入表准备分区的字段set create_table=create table larry( id int IDENTITY(1,1) NOT NULL, addtime datetime NOT NULL) -输入建表内容 如: create table larry( id int IDENTITY(1,1) NOT NULL, addtime datetime NOT NULL)set create_p_fun=CREATE PARTITION FUNCTION +table_name+_+p_field +(+p_field+) AS RANGE RIGHT FOR VALUES (+p_range+)set create_p_scheme=CREATE PARTITION SCHEME +table_name+_+p_field+_scheme AS PARTITION +table_name+_+p_field+ to (+p_scheme_filegroup+)set create_p_table=create_table+ on +table_name+_+p_field+_scheme(+table_filed+)print create_p_funprint create_p_schemeprint create_p_tableexec (create_p_fun)exec (create_p_scheme)exec (create_p_table)exec sp_configure show advanced options, 0RECONFIGUREGOEXEC sp_configure xp_cmdshell, 0GORECONFIGUREGO2，输入表名 查询分区函数 的函数use larryif exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_get_partition_funname) and xtype in (NFN, NIF, NTF) drop function dbo.f_get_partition_funname GO create function f_get_partition_funname(tablename sysname) returns nvarchar(260) as begin declare re varchar(50)set re = ( SELECTPartitionFunction = PF.nameFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id where TB.name = tablename) return(re) end go 3，输入表名 查询分区方案 的函数use larryif exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_get_partition_schemename) and xtype in (NFN, NIF, NTF) drop function dbo.f_get_partition_schemename GO create function f_get_partition_schemename(tablename sysname) returns nvarchar(260) as begin declare re varchar(50)set re = ( SELECTPartitionScheme = PS.nameFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id where TB.name = tablename) return(re) end go 4，数据表名 查询分区range 的脚本 declare re varchar(50) declare ii int declare sum int select partitionrange,identity(int,1,1) as id into #ttfrom ( SELECTpartitionrange = PR.valueFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id INNER JOIN sys.partition_range_values PR ON PS.function_id = PR.function_id where TB.name = larry -输入表名 ) la set re = +(select top 1 left(convert(varchar(50),partitionrange,120),10) from #tt)+set sum =(select count(*) from #tt)set ii = 1while ( ii sum)beginset re=(re+,+(select top 1 left(convert(varchar(50),partitionrange,120),10) from #tt where id=ii)+)set ii=ii+1 endprint re 5，输入表名，查询分区range的表值函数use larryif exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_get_partition_range) and xtype in (NFN, NIF, NTF) drop function dbo.f_get_partition_range GO create function f_get_partition_range(tablename sysname) returns table as return ( SELECTpartitionrange = PR.valueFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id INNER JOIN sys.partition_range_values PR ON PS.function_id = PR.function_id where TB.name = tablename ) go 6，查询数据库分区表，查询语句SELECTSchemaName = S.name,TableName = TB.name,PartitionScheme = PS.name,PartitionFunction = PF.name,PartitionFunctionRangeType = CASEWHEN boundary_value_on_right = 0 THEN LEFTELSE RIGHT END,PartitionFunctionFanout = PF.fanout,SchemaID = S.schema_id,ObjectID = TB.object_id,PartitionSchemeID = PS.data_space_id,PartitionFunctionID = PS.function_idFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_idGO-下边是加了rangeSELECTSchemaName = S.name,TableName = TB.name,PartitionScheme = PS.name,PartitionFunction = PF.name,partitionrange = PR.value,PartitionFunctionRangeType = CASEWHEN boundary_value_on_right = 0 THEN LEFTELSE RIGHT END,PartitionFunctionFanout = PF.fanout,SchemaID = S.schema_id,ObjectID = TB.object_id,PartitionSchemeID = PS.data_space_id,PartitionFunctionID = PS.function_idFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_idINNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id INNER JOIN sys.partition_range_values PR ON PS.function_id = PR.function_idGO-最简单的判断SELECTSchemaName = S.name,TableName = TB.nameFROM sys.schemas SINNER JOIN sys.tables TB ON S.schema_id = TB.schema_id INNER JOIN sys.indexes IDXon TB.object_id = IDX.object_id AND IDX.index_id 2INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_id7，增加分区表存储过程if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_addpartition) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_addpartition GO create proc p_addpartition tablename varchar(50)=, partition_range varchar(50)=,filegroup varchar(50)=as declare sql varchar(200)declare sql1 varchar(200)declare scheme varchar(50)declare p_fun varchar(50)set scheme = dbo.f_get_partition_schemename(tablename)set p_fun = dbo.f_get_partition_funname(tablename)set sql = ALTER PARTITION SCHEME + scheme+ NEXT USED +filegroup+set sql1 =ALTER PARTITION FUNCTION + p_fun+ () SPLIT RANGE (+partition_range+) -print sql -print sql1 exec (sql) exec (sql1) go-exec p_addpartition tablename=larry,partition_range=2010-01-01,filegroup=primary8，合并分区表存储过程if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_delpartition) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_delpartition GO create proc p_delpartition tablename varchar(50)=, partition_range varchar(50)=as declare sql varchar(200)declare p_fun varchar(50)set p_fun = dbo.f_get_partition_funname(tablename)set sql =ALTER PARTITION FUNCTION + p_fun+ () MERGE RANGE (+partition_range+) -print sql exec (sql) go-exec p_delpartition tablename=larry,partition_range=2010-01-019，统计分区表各分区的数据量if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_countpartition) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_countpartition GO create proc dbo.p_countpartition tablename varchar(50)=, tablefield varchar(50)=as declare sql varchar(20