sp_MSfetchidentityrange (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSfetchidentityrange(nvarchar @tablename
, bit @adjust_only
, nvarchar @table_owner)

MetaData:

 create procedure sys.sp_MSfetchidentityrange   
    @tablename          nvarchar(270),  
    @adjust_only        bit,  
    @table_owner                sysname = NULL  
AS  
  
declare @retcode            int  
declare @objid              int  
declare @artid uniqueidentifier  
declare @pubid uniqueidentifier  
declare @next_seed          bigint  
declare @range              bigint  
declare @identity_support   int  
declare @tablenick                      int  
declare @quoted_tablename   nvarchar(270)  
declare @is_republisher     bit  
declare @ident_current bigint  
declare @ident_increment bigint  
declare @range_begin numeric(38,0)  
declare @range_end numeric(38,0)  
declare @next_range_begin numeric(38,0)  
declare @next_range_end numeric(38,0)  
declare @max_used numeric(38,0)  
declare @threshold int  
  
if @table_owner is not NULL  
    select @quoted_tablename = QUOTENAME(@table_owner) + '.' + QUOTENAME(@tablename)  
else  
    select @quoted_tablename = quotename(@tablename)  
  
select @objid = object_id(@quoted_tablename)  
  
select @identity_support=identity_support, @tablenick = nickname, @artid=artid, @range=range, @threshold=threshold   
    from dbo.sysmergearticles where objid=@objid  
  
if @identity_support is NULL or @identity_support=0  
begin  
    --  table is not enabled for auto identity range management  
    raiserror(21197, 16, -1)  
    return (1)  
end  
  
--   
--  do permission checking  
 --   
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @tablenick=@tablenick  
if @retcode<>0 or @@ERROR<>0 return (1)  
  
--  check if this is a republisher.  
if exists (select pubid from dbo.sysmergearticles where artid=@artid and sys.fn_MSmerge_islocalpubid(pubid)=0)  
    select @is_republisher=1  
else  
    select @is_republisher=0  
  
select @pubid=subid from dbo.MSmerge_identity_range where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalsubid(subid)=1)  
if @pubid is NULL  
begin  
    raiserror(20663, 16, -1)  
    return (1)  
end  
  
  
--  get new identity. Now we do not know who the subscriber is. So it we cannot keep track  
--  of this subscriber. However we will allocate a new range and update the publisher's entry  
--  which indicates how much has been allocated.  
--  we will allocate a new identity irrespective of what @adjust_only has been set to. This is  
--  because the merge agent always calls mostly with @adjust_only being set to true and in the  
--  one case that it is set to false it should really be true.  
begin tran  
save tran fetchidentityrange  
  
select @ident_current = ISNULL(IDENT_CURRENT(@quoted_tablename), IDENT_SEED(@quoted_tablename))  
select @ident_increment = IDENT_INCR(@quoted_tablename)  
  
select @range_begin = range_begin,  
       @range_end = range_end,  
       @next_range_begin = next_range_begin,  
       @next_range_end = next_range_end,  
       @next_seed = max_used  
    from dbo.MSmerge_identity_range with (updlock, rowlock) where artid=@artid and subid=@pubid and is_pub_range=1   
if @range_begin is NULL or @range_end is NULL or @next_seed is NULL  
begin  
    raiserror(21197, 16, -1)  
    goto FAILURE  
end  
  
--  add one or subscract one from max_used for backward compatibility. This means between ranges we will always skip   
--  one number. That is fine if not Daytona will have overlapping ranges. Though SQL downlevel subscribers will be fine  
--  Daytona will have overlapping ranges and hence this increment.  
select @next_seed = @next_seed + @ident_increment  
  
--  the following is fine even in case of negative increments since the @range value is negative  
if @is_republisher=0  
begin  
    update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
    if @@error<>0  
    begin  
        raiserror(21197, 16, -1)  
        goto FAILURE  
    end  
end  
else  
begin  
    if @ident_increment>0  
    begin  
        if @range_end >= @next_seed and @range_begin <= @next_seed  
        begin  
            if (@next_seed+@range) <= @range_end  
            begin  
                --  there is enough space in the first range  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
            else  
            begin  
                --  we need to start using the second range  
                select @next_seed = @next_range_begin  
                if @next_range_begin is NULL  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
        end  
        else if @next_range_end >= @next_seed and @next_range_begin <= @next_seed  
        begin  
            if (@next_seed+@range) <= @next_range_end  
            begin  
                --  there is enough space in the second range  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
            else  
            begin  
                --  there is not enough range at the republisher to allocate for it subscriber  
                raiserror(20665, 16, -1)  
                goto FAILURE  
            end  
        end  
        else  
        begin  
            --  there is something terribly wrong here. @max_used is not in the ranges available at the publisher  
            raiserror(21197, 16, -1)  
            goto FAILURE  
        end  
    end  
    else  
    begin  
        if @range_end <= @next_seed and @range_begin >= @next_seed  
        begin  
            if (@next_seed+@range) >= @range_end  
            begin  
                --  there is enough space in the first range  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
            else  
            begin  
                --  we need to start using the second range  
                select @next_seed = @next_range_begin  
                if @next_range_begin is NULL  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
        end  
        else if @next_range_end <= @next_seed and @next_range_begin >= @next_seed  
        begin  
            if (@next_seed+@range) >= @next_range_end  
            begin  
                --  there is enough space in the second range  
                update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1  
                if @@error<>0  
                begin  
                    raiserror(21197, 16, -1)  
                    goto FAILURE  
                end  
            end  
            else  
            begin  
                --  there is not enough range at the republisher to allocate for it subscriber  
                raiserror(20665, 16, -1)  
                goto FAILURE  
            end  
        end  
        else  
        begin  
            --  there is something terribly wrong here. @max_used is not in the ranges available at the publisher  
            raiserror(21197, 16, -1)  
            goto FAILURE  
        end  
    end  
end  
commit tran  
  
select @range_begin = @next_seed, @range_end = @next_seed+@range  
  
if @is_republisher=0  
begin  
    declare @publication sysname  
    select @publication = name from dbo.sysmergepublications where pubid = @pubid  
  
    --  set the values about the current allocation on the distributor.  
    exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor      
                    NULL,  
                    @artid,   
                    0, --  is_pub_range 0 because we just allocated to a subscriber  
                    1, --  we allocated only one range since this a backward compat thing  
                    @range_begin,  
                    @range_end,  
                    NULL, --  next_range_begin is NULL because we are allocating only one range to a downlevel subscriber  
                    NULL, --  next_range_end is NULL because we are allocating only one range to a downlevel subscriber  
                    @publication,  
                    N'',  
                    N''  
end  
  
  
-- initialize article collection for agents.  
select @identity_support, @next_seed, @range, @threshold  
  
return 0  
FAILURE:  
    if @@trancount>0  
    begin  
        rollback tran fetchidentityrange  
        commit tran  
    end  
    return 1