|
if charindex("sa_role", show_role()) = 0
begin
print "You must have 'sa_role' to run this procedure."
return -1
end if charindex("sybase_ts_role", show_role()) = 0
begin
print "You must have 'sybase_ts_role' to run this procedure."
return -1
end -- get some info on the object
select @p0_id = object_id(@p0)
if @p0_id = NULL
begin
print " Error: '%1!' is not a user table.", @p0
return -1
end if @p0 not like "%.%"
begin
select @ntab = count(*) from sysobjects where name = @p0 and type = "U" if @ntab > 1
begin
print " %1! tables named '%2!' exist in this database:", @ntab, @p0
print " "
select owner + "." + name "owner.table_name"
from #id where name = @p0 order by uid
print " "
print " Specify 'owner.table_name' to identify the table."
return -1
end
end select @doampg = doampg, @indid = indid, @idgap = idgap
from #id where id = @p0_id if not exists (select 1 from syscolumns where id = @p0_id and status & 128 = 128)
begin
print " Error: Table '%1!' does not have an identity column", @p0
return -1
end -- retrieve the length of the numeric identity column as declared when the
-- table was created
select @len = prec, @colname = name
from syscolumns where id = @p0_id and status & 128 = 128
if @@rowcount = 0
begin
print " Error: Could not retrieve length of identity column for table '%1!'", @p0
return -1
end select @idlen = @len -- column precision (positions)
select @idlenb = (@idlen+1-((@idlen+5)/12)-(@idlen/12))/2 -- column length (bytes) print " "
print " Table = %1! (id=%2!)", @p0, @p0_id
print " Identity column = %1! numeric(%2!)", @colname, @len
--print " OAM page = %1!", @doampg
--print " Bytes on OAM page = %1!", @idlenb
if @idgap > 0
begin
print " Max. Identity Gap = %1! (""identity_gap"" = %2!)", @idgap, @idgap
end
else
begin
set arithabort numeric_truncation off
select @maxidgap = ceiling((convert(numeric(38),@idburn) * 0.0000001) * power(@n10,@len))
print " Max. Identity Gap = %1!", @maxidgap
print " (""identity burning set factor"" = %1! = %2!%%)", @idburn, @idburnpct
if @v = 12
begin
print " (Note: ""identity_gap"" has not been set for this table !)"
end
end
print " " if @p1 = NULL and @p2 = NULL -- retrieve current ID value
begin
print " "
print " Reading identity value from OAM page... (traceflag 3604 should be enabled !)"
print " "
dbcc object_atts (@p0, 0, get)
print " "
print " To decode this hexadecimal value, re-run 'sp_identity' with the hexadecimal"
print " string as a quoted 2nd parameter (you can leave the spaces in). "
print " Example: sp_identity %1!, ""the-hex-string-from-the-above-output"" ", @p0
print " "
--
-- only for 12.0+, dbcc listoam will display the values in the OAM and the DES
--
--dbcc listoam(@dbname, @p0_id, @indid)
--
return 0
end if upper(@p1) like "[ 0-9A-F]%" -- entered a hex string, decode it
begin if @p2 != NULL
begin
print " Error: When decoding a hex value from the OAM page, parameter 3"
print " (the new identity value) must be NULL."
exec sp_identity_help
return -1
end select @ib = @idlenb -- counts bytes
select @i = char_length(@p1) -- counts string
select @n = 0 -- holds result while 1 = 1
begin
select @c2 = substring(@p1, (char_length(@p1)-@i)+1, 2) --print "Step %1!, [%2!]", @ib, @c2
if upper(substring(@c2,1,1)) = " " -- ignore single space pasted in by user
begin
select @i = @i - 1
continue
end if (upper(@c2) not like "[0-9A-F][0-9A-F]")
begin
print " Error: Invalid characters (%1!) in hex string", @c2
break
end -- add this byte to the result
select @vc50 = @vc50 + @c2
select @j = power(@n256, (@ib-1))
select @n = @n + @j * hextoint("0x" + @c2)
--print "Step %1!, exp= %2!, result = %3!", @ib, @j, @n -- next byte
select @ib = @ib - 1
if @ib = 0 break -- ready -- next 2 chars
select @i = @i - 2
if @i = 0
begin
-- we shouldn't have got here, error
print " Error: ID column length and specified hex string do not match !"
break
end
end if @ib = 0
begin
print " Decoded identity value on OAM page = %1!", @n
print " (hex= %1!)", @vc50
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end if @p2 != NULL -- entered a new ID value, set it
begin if @p1 != NULL
begin
print " Error: When setting a new identity value, parameter 2"
print " (the hex string from the OAM page) must be NULL."
exec sp_identity_help
return -1
end -- set the new ID value
if @p2 <= 0
begin
print " Error: The new identity value must be >= 0."
print " "
exec sp_identity_help
return -1
end if char_length(convert(varchar, @p2)) > @idlen
begin
select @vc50 = replicate("9", @idlen)
print " Error: New identity value (%1!) is too large", @p2
print " for this identity column. The maximum possible value", @p2
print " is %1! (%2! positions).", @vc50, @idlen
return -1
end if @lsb = 0
begin
print " "
print "Internal error in 'sp_identity':"
print "Cannot figure out byte order on this platform."
print "Please send a note to 'rob@sypron.nl' with the text of this"
print "error and your @@version string."
return -1
end -- convert the numeric value to hex
select @ib = @idlenb -- counts bytes
select @i = 15 select @vc50 = NULL -- holds result
select @vb16 = NULL -- holds result
select @n256 = 256
select @n = @p2
set arithabort numeric_truncation off while 1 = 1
begin
select @j = power(@n256, @i) select @b = @n / @j
select @n = @n - (@b * @j)
select @c2 = right(inttohex(@b),2)
select @b1 = substring(convert(binary(4), @b), @lsb,1) select @vb16 = @vb16 + @b1 --print "Step %1!, @b=%2!, hex(c2)=%3!, hex(b1)=%5!, result=%4!", @i, @b, @c2, @vb16, @b1 select @i = @i - 1
if @i < 0 break -- ready
end if @i < 0
begin
select @vb16 = substring(@vb16, 16 - (@idlenb) + 1, (@idlenb) ) + substring(@vb16, 1, 16 - (@idlenb) ) -- set the new ID value
dbcc object_atts(@p0, 0, "put", @vb16) print " "
print " Identity value on OAM page has been set to %1!", @p2
print " (hex= %1!)", @vb16
print " "
select @p2 = @p2 + 1
print " You should now do a 'shutdown with nowait' immediately."
print " After restarting the server, the value assigned to the next row "
print " inserted into '%1!' will be %2!.", @p0, @p2
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end -- we should never get here
print "Internal error in 'sp_identity': Reached invalid end of procedure."
return -1
end
go grant execute on sp_identity to public
dump tran sybsystemprocs with truncate_only
go
print "Ready. For usage information, run 'sp_identity ""?"" '."
print ""
go
/*
** end
*/
| 
|Archiver|PowerBuilder(PB)BBS社区
( 鲁ICP备2021027222号-1 )
