[转帖]修复identity 类型字段数据的跳跃续1

JohnPhan 发表于 2007-3-23 12:25:46

下面是SP_IDENTITY.120.SQL，用于  12.0 以后版本 /*   * SP_IDENTITY.120.SQL - version for ASE 12.0 or later  * (when running ASE 11.9/11.5/11.0, install SP_IDENTITY.119.SQL instead)  *   * Description  * ===========  * This file contains the stored procedure 'sp_identity', which performs some   * useful functions w.r.t. identity columns:  *  *   - displays all tables with an identity column in the current database  *   - displays the identity value stored on a table's OAM page  *   - resets the identity value stored on a table's OAM page  *  * For details and background, see <a href="http://www.sypron.nl/idfix.html">http://www.sypron.nl/idfix.html</a> .  *  *  * Installation  * ============  * Execute this script using "isql", using a login having both "sa_role"  * and "sso_role".  * The stored procedure will be created in the sybsystemprocs database.  *  *  * Notes  * =====  * - Traceflag 3604 should be enabled before running sp_identity for a   *   specific table ("dbcc traceon(3604)"); if omitted, you won't see   *   any output.  *  * - To run sp_identity for a specific table, sa_role and sybase_ts_role   *   are required.  *  * - sp_identity was successfully tested on ASE versions 11.0.3, 11.5,   *   11.9.2 and 12.0 on various platforms.  *  * - sp_identity will not work on ASE 11.0 because this version   *   does not support the 'case' expression.  *  * - sp_identity was not tested on a 64-bit ASE version.  *  *  * Revision History  * ================  * Version 1.0  17-Dec-2000  First version  * Version 1.1  Oct-2002  Handle identity_gap=NULL (thanks to Alan Cooper),  *                        and some small improvements for installation  * Version 1.2  Apr-2003  Improved installation  *   *  * Copyright Note & Disclaimer :  * =============================  * This software is provided "as is"; there is no warranty of any kind.  * While this software is believed to work accurately, it may not work   * correctly and/or reliably in a production environment. In no event shall    * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting   * from the use of this software.   * You are allowed to use this software free of charge for your own   * professional, non-commercial purposes.   * You are not allowed to sell or bundle this software or use it for any   * other commercial purpose without prior written permission from   * Rob Verschoor/Sypron B.V.  * You may (re)distribute only unaltered copies of this software, which   * must include this copyright note, as well as the copyright note in   * the header of each stored procedure.  *  * Note: All trademarks are acknowledged.  *  * Please send any comments, bugs, suggestions etc. to the below email  * address.  *  * Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V.  *                    P.O.Box 10695  *                    2501 HR Den Haag  *                    The Netherlands  *  *                    Email: <a href="mailto:rob@sypron.nl">rob@sypron.nl</a>
  *                    WWW  : <a href="http://www.sypron.nl/">http://www.sypron.nl/</a>
  *----------------------------------------------------------------------------   */ set nocount on go set flushmessage on go use sybsystemprocs go -- we need to be at ASE 12.0 or later; if not, abort this script if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99 begin    print ""    print ""    print "********************************************"    print "********************************************"    print " This script is for ASE 12.0 or later."    print " Please install SP_IDENTITY.119.SQL instead."    print "********************************************"    print "********************************************"    print " "    print " "    print ""    set background on  -- terminate this script now end go print "" print "Installing 'sp_identity'..." print "" go

JohnPhan 发表于 2007-3-23 12:28:12

回复：（JohnPhan）[转帖]修复identity 类型字段数据的...

if object_id("sp_identity_help") <> NULL begin    drop proc sp_identity_help end go create proc sp_identity_help /* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */ as begin    print " "    print " Usage: sp_identity table_name, ""hex-string-from-OAM-page"", new-identity-value "    print " Notes: "    print " - specifying only parameter 1 retrieves the current identity value for"    print "   that table from the OAM page as a hexadecimal string."    print " - specifying this hexadecimal string as the second parameter (in quotes)"    print "   will decode the hex value to a numeric value."    print " - specifying a numeric value for the third parameter will set that"    print "   value as the new identity value for this table."    print " - parameters 2 and 3 cannot be specified together: one of them must be NULL."    print " - ""dbcc traceon(3604)"" must be run before using option 2 or 3"    print " "    print " Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V."    print " See <a href="http://www.sypron.nl/idfix.html">http://www.sypron.nl/idfix.html</a> for background information & updates."    print " " end go grant execute on sp_identity_help to public dump tran sybsystemprocs with truncate_only go if object_id("sp_identity") <> NULL begin    drop proc sp_identity end go create proc sp_identity /* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */  @p0 varchar(50) = NULL, -- table name  @p1 varchar(50) = NULL, -- hex value to decode  @p2 numeric(38) = NULL  -- new value to set as begin set nocount on declare @n numeric(38), @j numeric(38), @n256 numeric(3), @n10 numeric(38) declare @i int, @max int, @idlen int, @idlenb int, @ib int, @len int declare @p0_id int, @b int, @b1 binary(1), @lsb int declare @idgap int, @idburn int, @maxidgap numeric(38), @max1 int, @max2 int declare @idburnpct numeric(5,2), @v int, @ntab int declare @c2 char(2), @doampg int, @indid int, @dbname varchar(32) declare @vc50 varchar(50), @colname varchar(32), @vb16 varbinary(16) select @dbname = db_name() -- check version select @v = 0 if exists (select * from sysobjects             where name = "sysqueryplans" and type = "S") begin     select @v = 12 end if @p0 = '?' begin    exec sp_identity_help    return 0 end -- numeric values select @n256 = 256 select @n10 = 10 -- id burning set factor select @idburn = value from master.dbo.syscurconfigs where config=141 select @idburnpct = convert(numeric(5,2), @idburn * 0.00001) -- figure out msb/lsb select @lsb = 0 if substring(convert(binary(4), 1), 1 ,1) = 0x01 select @lsb = 1 if substring(convert(binary(4), 1), 2 ,1) = 0x01 select @lsb = 2 if substring(convert(binary(4), 1), 3 ,1) = 0x01 select @lsb = 3 if substring(convert(binary(4), 1), 4 ,1) = 0x01 select @lsb = 4 -- get all tables containing identity columns in this database select id = so.id, owner = su.name, uid = so.uid, so.name, si.doampg, si.indid, idgap =  isnull(si.identitygap,0),        colname = sc.name, sc.prec, maxgap = convert(numeric(38), ceiling((@idburn * 0.0000001) *  power(@n10,prec))) into #id from sysindexes si, syscolumns sc, sysobjects so, sysusers su where si.indid < 2    and so.type = "U"    and sc.status & 128 = 128   and so.id = sc.id    and so.id = si.id   and so.uid = su.uid select @ntab = @@rowcount set arithabort numeric_truncation off if @p0 = null begin    -- display all tables with identity columns in the current DB    if @ntab = 0    begin       print " There are no tables with an identity column in database '%1!'", @dbname   return 0    end    print " Tables with an identity column in database '%1!':", @dbname    print " "    select @max1 = max(char_length(owner + "." + name + "." + colname)) + 12 from #id    select @max2 = max(char_length(convert(varchar(38), maxgap))) + 7 from #id    if @max1 <= 52 and @max2 <= 24    begin      select convert(varchar(52), owner + "." + name + "." + colname + " numeric(" +  convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",     convert(varchar(24), case when idgap = 0 then convert(varchar(38),maxgap) + "  (burn)"        else convert(varchar, idgap) + " (identity_gap)"      end) "Maximum Identity Gap"      from #id      order by name    end    else    if @max1 <= 32 and @max2 <= 44    begin      select convert(varchar(32), owner + "." + name + "." + colname + " numeric(" +  convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",     convert(varchar(44), case when idgap = 0 then convert(varchar(38),maxgap) + "  (burn)"        else convert(varchar, idgap) + " (identity_gap)"      end) "Maximum Identity Gap"      from #id      order by name    end    else    begin      select convert(varchar(78), owner + "." + name + "." + colname + " numeric(" +  convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",     convert(varchar(50), case when idgap = 0 then convert(varchar(38),maxgap) + "  (burn)"        else convert(varchar, idgap) + " (identity_gap)"   end) "Maximum Identity Gap"       from #id       order by name    end    print " "    print " Legend:"    print "    (burn)         : gap size is determined by ""identity burning set factor"" "    if @v = 12    begin    print "    (identity_gap) : gap size is determined by the ""identity_gap"" setting"    end    print " "    print " Current value for ""identity burning set factor"" = %1! (=%2!%%)", @idburn, @idburnpct    return 0 end

JohnPhan 发表于 2007-3-23 12:28:48

回复：（JohnPhan）[转帖]修复identity 类型字段数据的...

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 "")     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 <a href="mailto:'rob@sypron.nl'">'rob@sypron.nl'</a> 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 */

页: [1]

PowerBuilder(PB)BBS社区's Archiver