create procedure sys.sp_MScompute_maxmin_identity
@objid int,
@max_identity numeric(38,0) output,
@min_identity numeric(38,0) output
as
declare @xtype tinyint
declare @xprec tinyint
select @xtype=system_type_id, @xprec=precision from sys.columns where object_id=@objid and is_identity=1
if @xtype is NULL
begin
raiserror(20706, 16, -1)
return 1
end
if (@xtype = 48) -- tinyint
begin
select @max_identity = power((convert(bigint,2)), 8-1) - 1
select @min_identity = power((convert(bigint,-2)), 8-1)
end
if (@xtype = 52) -- smallint
begin
select @max_identity = power((convert(bigint,2)), 8*2-1) - 1
select @min_identity = power((convert(bigint,-2)), 8*2-1)
end
if (@xtype = 56) -- int
begin
select @max_identity = power((convert(bigint,2)), 8*4-1) - 1
select @min_identity = power((convert(bigint,-2)), 8*4-1)
end
if (@xtype = 127) -- bigint
begin
select @max_identity = power((convert(bigint,2)), 62) - 1 + power((convert(bigint,2)), 62)
select @min_identity = power((convert(bigint,-2)), 63)
end
if (@xtype=108 or @xtype=106) -- numeric or decimal
begin
select @max_identity = convert(numeric(38,0), replicate('9',@xprec))
select @min_identity = -@max_identity
end
return 0