/*
 * IDgapsRevisited.SQL
 * 
 * Description
 * ===========
 * This file contains the following stored procedures, as mentioned
 * in the article 'Identity gaps revisited' in the 2004 Q2 issue of the
 * ISUG Technical Journal:
 * 
 *    - sp_identity_remove
 *      Temporarily suppresses the identity behaviour of an identity column
 *      by flipping some bits in system tables. This is useful in pre-12.5.0.3
 *      only: in 12.5.0.3+, use the command 'set identity_update ... on/off' 
 *      instead. Note that this procedure is not supported by Sybase. Make sure 
 *      no users are accessing the table when this procedure is running.
 *      For more details about this procedure, see chapter 11 of my book 
 *      "Tips, Tricks & Recipes for Sybase ASE" (www.sypron.nl/ttr).
 *
 *    - sp_identity_restore
 *      Restores the identity behaviour of an identity column that was suppressed
 *      by 'sp_identity_remove'. Note that this procedure is not supported by Sybase.
 *
 *    - sp_idgap_repair
 *      Detects and repairs an identity gap in a table in ASE 12.5.1+.
 *      
 *    - sp_chgattribute (with the new option 'identity_burn_max_force')
 *      Resets the identity column counter to any desired value, without performing 
 *      any checks against the current values in the table. This procedure can only 
 *      be used in ASE 12.5.1+.
 *
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having 'sa_role' and 'sso_role'.
 * The stored procedures will be created in the sybsystemprocs database.
 *
 *
 * Parameters
 * ==========
 * Run each procedure (except sp_chgattribute) with '?' as a single parameter  
 * to get a description of the expected parameters.
 *
 *
 * Revision History
 * ================
 * Version 1.0    Apr-2004  Bundled various existing procs for the ISUG 
 *                          TechJournal article.
 * Version 1.1    Jan-2006  Added support for 15.0 version of sp_chgattribute
 * 
 *
 * 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) 2004-2006 Rob Verschoor/Sypron B.V.
 *                         P.O.Box 10695
 *                         2501 HR Den Haag
 *                         The Netherlands
 *
 *                         Email: sypron@sypron.nl
 *                         WWW  : http://www.sypron.nl/
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go

use sybsystemprocs
go

-- we need sa_role
if proc_role("sa_role") = 0
or proc_role("sso_role") = 0
begin
   print ""
   print ""
   print " 'sa_role' and 'sso_role' are required to run this script."
   print " Aborting..."
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

print " Copyright (c) 2004-2006 Rob Verschoor/Sypron B.V."

-----------------------------------------------------------------

dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go

print ""
print " Installing 'sp_identity_remove' ..."
print ""
go

sp_configure 'allow up', 1
go

if object_id('sp_identity_remove') <> NULL
begin
   drop procedure sp_identity_remove
end
go

create procedure sp_identity_remove
/* Copyright (c) 2004-2006 Rob Verschoor/Sypron B.V. */
   @table_name varchar(30),
   @column_name varchar(30)
as
begin
   declare @id int

   if @table_name = NULL or @table_name = '?'
   begin
      print "Usage: sp_identity_remove 'table_name', 'column_name'"
      return
   end

   -- Note: we're not handling 'owner.table' format here
   select @id = so.id from sysobjects so, syscolumns sc
   where so.name = @table_name
     and sc.name = @column_name
     and so.id   = sc.id
     and so.type = 'U'
     and so.sysstat2 & 64 = 64
     and sc.status & 128  = 128

   if @@rowcount = 0
   begin
      print 'No such table or identity column'
      return
   end

   -- Table ID has been found; now remove the bits
   begin transaction
      update syscolumns
      set status = status & ~128  -- clear this bit
      where id   = @id
	    and name = @column_name
	    and status & 128 = 128

      if @@rowcount != 1 or @@error != 0
      begin
	     print 'Error updating syscolumns: rolling back...'
	     rollback
	     return
      end

      update sysobjects
      set sysstat2 = sysstat2 & ~64  -- clear this bit
      where id     = @id
	    and sysstat2 & 64 = 64

      if @@rowcount != 1 or @@error != 0
      begin
	     print 'Error updating sysobjects: rolling back...'
	     rollback
	     return
      end
   commit

   print "Identity property is removed from '%1!.%2!'",
         @table_name, @column_name
end
go

-----------------------------------------------------------------

dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go

print ""
print " Installing 'sp_identity_restore' ..."
print ""
go

if object_id('sp_identity_restore') <> NULL
begin
   drop procedure sp_identity_restore
end
go

create procedure sp_identity_restore
/* Copyright (c) 2004-2006 Rob Verschoor/Sypron B.V. */
   @table_name varchar(30),
   @column_name varchar(30)
as
begin
   declare @id int, @type int, @scale int
   declare @colname2 varchar(30)

   if @table_name = NULL or @table_name = '?'
   begin
      print "Usage: sp_identity_restore 'table_name', 'column_name'"
      return
   end

   -- Note: we're not handling 'owner.table' format here
   select @id = so.id, @type = sc.type, @scale = sc.scale
   from sysobjects so, syscolumns sc
   where so.name = @table_name
     and sc.name = @column_name
     and so.id = sc.id
     and so.type = 'U'

   if @@rowcount = 0
   begin
      print 'No such table or column'
      return
   end

   -- Check datatype is numeric(x,0)
   if @type not in (63, 108) and
      @scale != 0
   begin
      print "Column '%1!' must be 'numeric' with scale=0",
            @column_name
      return
   end

   -- Allow only 1 identity column per table
   select @colname2 = name from syscolumns 
   where id     = @id
     and name  != @column_name
     and status & 128 = 128

   if @@rowcount > 0
   begin
      print "Column '%1!' is already an identity column in %2!",
            @colname2, @table_name
      return
   end

   begin transaction
      update syscolumns
      set status = status | 128    -- set this bit
      where id   = @id
	    and name = @column_name 
	    and status & 128 = 0

      if @@rowcount != 1 or @@error != 0
      begin
	     print 'Error updating syscolumns: rolling back...'
	     rollback
	     return
      end

      update sysobjects
      set sysstat2 = sysstat2 | 64   -- set this bit
      where id     = @id
	    and sysstat2 & 64 = 0

      if @@rowcount != 1 or @@error != 0
      begin
	     print 'Error updating sysobjects: rolling back...'
	     rollback
	     return
      end
   commit

   print "Identity property is restored for '%1!.%2!'",
         @table_name, @column_name
end
go

sp_configure 'allow up', 0
go

-----------------------------------------------------------------

--
-- 'sp_idgap_repair'
--
-- this can only be installed in 12.5.1+
--

-- First check for 12.0+
if object_id("sysqueryplans") = NULL
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " You must run ASE version 12.5.1 or later"
   print " to install these stored procedures."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print " "
   print "Quitting now..."
   print " "
   set background on   -- terminate this script now
end
go

--
-- Check for 12.5.1+
--
if license_enabled('ASE_XML') = NULL 
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " You must run ASE version 12.5.1 or later"
   print " to install these stored procedures."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print " "
   print "Quitting now..."
   print " "
   select syb_quit()  -- terminate this script now
end
go

dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go

print ""
print " Installing 'sp_idgap_repair' ..."
print ""
go

if object_id('sp_idgap_repair') <> NULL
begin
   drop proc sp_idgap_repair
end
go

create proc sp_idgap_repair
/* Copyright (c) 2004-2006 Rob Verschoor/Sypron B.V. */
   @table_name varchar(30) = NULL,  -- the table in question
   @gap_hi numeric(38)     = NULL,  -- the lowest value on the upper end of the gap
   @gap_reset numeric(38)  = NULL   -- the value @gap_hi should be reset to
as
	declare @cmd varchar(500)
	declare @nextid numeric(38), @burnid numeric(38), @maxid numeric(38), @minid numeric(38)
	declare @maxid_new numeric(38), @minid_new numeric(38)
	declare @gap_reset_str varchar(38)
	declare @gapsize numeric(38)
	declare @colname varchar(30), @idgap int, @id int

    if @table_name = NULL or @table_name = '?'
    begin
       print "Usage: sp_idgap_repair table_name, high_value, reset_value "
	   print "   ==> Resets identity column values starting at high_value "
	   print "   ==> to reset_value (and higher)." 
	   print "Example: sp_idgap_repair MyTable, 500000002, 10032" 
	   print " " 
       print "Usage: sp_idgap_repair table_name "
	   print "   ==> Immediately after  ASE startup, checks whether an" 
	   print "   ==> identity gap currently exists; if so, fixes it." 
	   print " " 
       return 0
    end

    -- Retrieve the identity column name
	-- Note that we cannot currently handle 'owner.table_name' format...
    select @colname = sc.name, @id = so.id
    from sysobjects so, syscolumns sc
    where so.name = @table_name
      and so.id   = sc.id
      and so.type = 'U'
      and so.sysstat2 & 64 = 64
      and sc.status & 128  = 128
	
	if @@rowcount = 0
	begin
	   print "Table '%1!' was not found, or does not contain an identity column.", @table_name
	   return 0
	end
	
	select @idgap = isnull(identitygap, 0)
	from sysindexes 
	where id = @id
	  and indid < 2

	-- ID col found
    print " "
	print "Table '%1!' has identity column '%2!'.", @table_name, @colname
	print " "

	-- report warning if 'identity_gap' has not been defined
	if @idgap = 0
	begin
	   print "*** NOTE ***"
	   print "The 'identity_gap' property has not been set for table '%1!'.", @table_name
	   print "This can result in large 'identity gaps' in a number of situations."
	   print "It is *strongly* recommended to set identity_gap *now* with this command:"
	   print "    exec sp_chgattribute '%1!', 'identity_gap', 100 ", @table_name
	   print "(in this example, the maximum size of an identity gap will be 100; you may "
	   print " choose a different value instead)."
	   print " "
	end

---------------------------------------------------------------------------------------
	--
	-- Case 1: no gap start value specified, check if the next insert would 
	-- lead to an identity gap, defined here as a > 1000 units jump. It would
	-- be better to take the current setting of 'identity_gap' or 'identity burning set factor'
	-- into account but there was no time to implement this. So let's call this an
	-- 'exercise for the reader' ;-)
	--
    if @gap_hi = NULL
	begin
     	select @nextid = convert(numeric(38), next_identity(@table_name))
		select @burnid = convert(numeric(38), identity_burn_max(@table_name))

		if @nextid - @burnid != 1
		begin
		   --
		   -- note that this test may not work properly for small values of identity_gap, but
		   -- we'll take that for granted since these aren't problem cases anyway
		   --
		   print "Diagnosis: Rows have been inserted into this table since the last "
		   print "ASE restart. In this situation, it is not possible to determine the existence"
		   print "or absence of an identity gap automatically."
		   print " "
		   print "If you believe this table contains an identity gap in its current rows, run "
		   print "'sp_idgap_repair table_name, high_value, reset_value', where 'high_value' is"
		   print "the lowest identity value on the upper side of the identity gap, and"
		   print "'reset_value' is the value it should be reset to."
		   print "Run 'sp_idgap_repair table_name' (without additional parameters) only directly"
		   print "after ASE startup, before any new rows are inserted."
		   return 0
		end

		create table #id(maxid numeric(38) null)
		select @cmd = "insert #id select max(SYB_IDENTITY) from " + @table_name
		exec(@cmd)
		select @maxid = maxid from #id
		select @maxid = isnull(@maxid, @nextid)
		drop table #id

        -- report what we've found
		print "Table '%1!': currently, the highest value for the identity column for an", @table_name
		print "existing row is %1!. The identity value assigned to the next", @maxid
		print "inserted row is %1!.", @nextid
		print " "

        -- do we have a gap?
		if @nextid > @maxid + 1000   -- '1000' is an arbitrary criterium indeed...
		begin
		    print "Diagnosis: There seems to be an identity gap in table '%1!'.", @table_name
		    print " "

			select @gap_reset_str = convert(varchar(38), @maxid)
			exec sp_chgattribute @table_name, 'identity_burn_max', 0, @gap_reset_str

		    print "Action: Identity gap for table '%1!' has been fixed.", @table_name
			select @maxid = @maxid + 1
		    print "The next inserted row will get value %1!.", @maxid
		end
		else
		begin
		   print "Diagnosis: There does not seem to be an identity gap in table '%1!'.", @table_name
		   print "Action   : None."
		end

		return 0
	end

---------------------------------------------------------------------------------------

    --
	-- Case 2: gap start value specified; consider everything above that value a gap
	--
	if @gap_hi <= @gap_reset
	begin
       print "Usage: sp_idgap_repair 'table_name', high_value, reset_value "
	   print "   ==> Resets identity column values starting at high_value "
	   print "   ==> to reset_value (and higher)." 
	   print "Example: sp_idgap_repair MyTable, 500000002, 10032" 
	   print " " 
	   print "   Note: high_value must be > reset_value."
       return 0
	end

	if @gap_reset < 1
	begin
       print "Usage: sp_idgap_repair 'table_name', high_value, reset_value "
	   print "   ==> Resets identity column values starting at high_value "
	   print "   ==> to reset_value (and higher)." 
	   print "Example: sp_idgap_repair MyTable, 500000002, 10032" 
	   print " " 
	   print "   Note: reset_value must be > 0."
       return 0
	end

    select @gapsize = @gap_hi - @gap_reset

	create table #id2(n int, id numeric(38) null)
	select @cmd = "insert #id2 select 1, count(*) from " + @table_name + 
	              " where SYB_IDENTITY = " +  convert(varchar(38),@gap_reset) 
	exec(@cmd)

	select @cmd = "insert #id2 select 2, min(SYB_IDENTITY) from " + @table_name + 
	              " where SYB_IDENTITY > " +  convert(varchar(38),@gap_reset) 
	exec(@cmd)

	select @cmd = "insert #id2 select 3, max(SYB_IDENTITY) from " + @table_name + 
	              " where SYB_IDENTITY > " +  convert(varchar(38),@gap_reset) 
	exec(@cmd)

	select @nextid = id from #id2 where n = 1
	if isnull(@nextid,0) > 0
	begin
	   print "Error: A row with value %1! already exists in the table.", @gap_reset
	   print "The parameter '@gap_reset' specifies the value to which the 'high'"
	   print "identity values must be reset; no row with this value must exist yet."
	   return 0
	end
	
	select @minid = id from #id2 where n = 2
	if @minid = NULL
	begin
	   print "Error: There are no values > %1!, so there does not seem to be an identity gap...", @gap_reset
	   return 0
	end
	if @minid != @gap_hi
	begin
	   print "Error: %1! is not the lowest value on the upper end of the identity gap", @gap_hi
	   print "(this appears to be %1! instead)", @minid
	   print "The parameter '@gap_hi' must specify the lowest value > '@gap_reset'."
	   print "You should probably run the following command instead:"
	   print "    exec sp_idgap_repair '%1!', %2!, %3!", @table_name, @minid, @gap_reset
	   return 0
	end

    -- to determine where the identity counter must continue
	select @maxid = id from #id2 where n = 3

	drop table #id2
	
    -- Create the update statement
	select @cmd = "set identity_update " + @table_name + " on"
	select @cmd = @cmd + " update " + @table_name + " set SYB_IDENTITY = SYB_IDENTITY - " + convert(varchar(38), @gapsize) +
	                     " where SYB_IDENTITY >= " +  convert(varchar(38),@gap_hi)
    select @cmd = @cmd + " set identity_update " + @table_name + " off"
	--print "cmd=[%1!]", @cmd

    -- Do the update. Note that update triggers or RI constraints may be in the way, but
	-- we'll leave that to the DBA...
	select @minid_new = @minid - @gapsize
	select @maxid_new = @maxid - @gapsize
	print "Action: Updating identity column values %1!-%2! to %3!-%4! ...", @minid, @maxid, @minid_new, @maxid_new
    begin transaction
	exec(@cmd)
	if @@error != 0
	begin
	   print "*** Error during update -- rolling back"
	   rollback
	   return 0
	end
	commit
	print " "

    -- Now reset the identity counter to continue at the right value
    select @gap_reset_str = convert(varchar(38), @maxid_new)
	exec sp_chgattribute @table_name, 'identity_burn_max', 0, @gap_reset_str

    -- Ready !
	print " "
	print "Action: Identity gap for table '%1!' has been fixed.", @table_name
go

-----------------------------------------------------------------

dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go

--
-- Modified version of 'sp_chgattribute'
--

--
-- This can only be installed in 12.5.1+
-- We have already verified this above ...
--

print " "
print "******* N O T E ************"
print "This script installs a modified version of 'sp_chgattribute',"
print "containing a new option 'identity_burn_max_force'. "
print "See the article 'Identity Gaps Revisited' (downloadable from"
print "www.sypron.nl/idgaps.html) for more information. "
print " "
print "This version of 'sp_chgattribute' is based on the originals "
print "taken from ASE 12.5.x or ASE 15.0 (the correct version will be"
print "installed automatically)."
print "The changed lines of SQL code (about 10) are indicated by the "
print "string 'RobV' in the source. "
print "In future ASE versions, Sybase may well introduce additional "
print "or changed functionality in the standard version of 'sp_chgattribute'."
print "By installing this version of 'sp_chgattribute', you may effectively "
print "loose those changes so please verify whether this applies. "
print " "
print "To reinstall the original version of sp_chgattribute, re-run the "
print "'installmaster' (on Windows: 'instmstr') script."
print " "
print "Note that installing this version of 'sp_chgattribute' is at entirely"
print "at your own risk. Sypron B.V. does not accept any responsability for"
print "any consequences."
print " "
print "The original 'sp_chgattribute' is copyrighted material by Sybase, Inc."
print "****************************"
print " "
go

sp_configure 'allow up', 1
go

-- RobV: start add option to spt_values --------------
-- Add the new option to 'spt_values'. Note that we're using '-1' here to avoid
-- a clash with future options that may be added to sp_chgattribute, since those
-- will likely have a positive sequence number

delete master.dbo.spt_values 
where name = 'identity_burn_max_force'
  and type = 'H'

insert master.dbo.spt_values 
values ('identity_burn_max_force', -1, 'H', NULL, NULL, NULL, NULL)
go
-- RobV: end add option to spt_values --------------

print ""
print " Installing modified version of 'sp_chgattribute' ..."
print ""
go

if object_id('sp_chgattribute') <> NULL
begin
   drop proc sp_chgattribute
end
go

------ RobV: modified version of sp_chgattribute (12.5) starts here ------------------
/*
** Messages for "sp_chgattribute"
**
** 17260, "Can't run %1! from within a transaction."
** 17460, "Object must be in the current database."
** 17782, "You do not own a table or column (or index) of that name in the current database."
** 17756, "The execution of the stored procedure '%1!' in database
**         '%2!' was aborted because there was an error in writing the
**         replication log record."
** 18121, "Unrecognized change attribute option."
** 18122, "'%1!' attribute of object '%2!' changed to %3!."
** 18571, "The attribute '%1!' is not applicable to tables with allpages lock scheme."
** 18572, "The value for attribute '%1!' must be between %2! and %3!."
** 18573, "Failed to update attribute '%1!' for object '%2!'."
** 18612, "Invalid value specified for option '%1!'. Valid range of values is %2! to %3!."
** 18613, "The attribute '%1!' is applicable to tables only."
** 18840,  The value specified for identity gap %1! is not legal; identity gap must be greater than or equal to 0.
** 18983, "The '%1!' attribute is not applicable to tables with datarow or datapage lock schemes."
** 18985, "The value for '%1!' attribute must be either 0 or 1."
** 18987, "The '%1!' attribute could not be set for the object."
** 19115, "A value must be specified for attribute '%1!'."
** 19116, "Table '%1!' does not have an identity column."
** 19117, "The value %1! for '%2!' attribute must be greater than or equal to the current max identity value %3!."
** 19118, "Object '%1!' is currently being accessed by other users. Failed to update attribute '%2!'." 
**
** For the option dealloc_first_txtpg we use the internal procedure sp_optimal_text_space
** to perform the necessary checks/updates. This procedure could raise additional
** messages.
*/

create procedure sp_chgattribute
@objname varchar(92),                   /* table or index name */
@optname varchar(30),
@optvalue int,
@optvalue2 varchar(38) = NULL
as

declare @objid int                      /* object id of the table */
declare @indid smallint                 /* table's index id  */
declare @msg varchar(1024)
declare @dbname varchar(30)
declare @dbid	int			/* database id */
declare @tabname varchar(30)            /* name of table */
declare @indname varchar(30)            /* name of index */
declare @varcol_count smallint		/* Number of variable length columns */
declare @sysstat2 int                   /* status bits of the table */
declare @is_table_APL int               /* flag to distinguish APL and DOl tables */
declare @dbcc_arg_3 smallint		/* third argument to dbcc command. 
					** This must be of type smallint.
					*/
declare @newoptvalue varchar(10)	/* Used for converting the optvalue */
declare @opt_ind_lock int		/* server status for optimistic index lock */
declare @curlimit16 smallint		/* current value for int16 column */
declare @newlimit16 smallint		/* new value for int16 column */
declare @curlimit32 int			/* current value for int32 column */
declare @newlimit32 int			/* new value for int32 column */
declare @dbcc_arg_4 int			/* forth argument to dbcc command. 
					** This must be of type int.
					*/
declare @retstatus int
declare @curr_max_idtval numeric(38, 0)	/* current max id value in the table. */
declare @new_idtval numeric(38,0)	/* numeric number of input optvalue2 */
declare @colname varchar(30)		/* name of identity column */
declare @sqltext varchar(100)		/* tmp holder for sqltext */

/* If we're in a transaction, disallow this */
if @@trancount > 0
begin
	/*
	** 17260, "Can't run %1! from within a transaction."
	*/
	raiserror 17260, "sp_chgattribute"
	return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1

if @objname like "%.%.%"
begin
	/*
	** 17460, "Object must be in the current database."
	*/
	raiserror 17460
	return (1)
end

if not exists (
	select * from master.dbo.spt_values 
		 where type = "H"
		 and  name = @optname)
begin
	/*
	** 18121, "Unrecognized change attribute option."
	*/
	raiserror 18121
	return (1)
end

if @objname like "%.%"
begin
	/*
	** Attributes 'exp_row_size' and 'concurrency_opt_threshold' apply 
	** only to a table and not to an index 
	*/	
	if (@optname = "concurrency_opt_threshold" or 
	    @optname = "exp_row_size" or
	    @optname = "optimistic_index_lock")
	begin
		/*
		** 18613, "The attribute '%1!' is applicable to tables only."
		*/
		raiserror 18613, @optname
		return (1)
	end	
		
	/*
	**  Get the table name and index name out.
	*/
	select @tabname = substring(@objname, 1, charindex(".", @objname) - 1)
	select @indname = substring(@objname, charindex(".", @objname) + 1, 61)

	select @objid = si.id, @indid = si.indid
		from sysobjects so, sysindexes si
		where 	si.id = so.id
			and so.name = @tabname
			and si.name = @indname
			and si.indid != 0
			and so.sysstat & 7 = 3  /* user table */
			and so.uid = user_id()
end
else
begin
	select @tabname = @objname
	select @objid = si.id, @indid=si.indid
		from sysobjects so, sysindexes si
		where 	so.name = @tabname
			and si.id = so.id
			and si.indid in (0,1)
			and so.sysstat & 7 = 3  /* user table */
			and so.uid = user_id()
end
/*
**  If @objid is still NULL then that means it isn't a 
**  table/index name.  
*/
if (@objid is null)
begin
	/*
	** 17782, "You do not own a table, column or index of that name in 
	** the current database."
	*/
	raiserror 17782
	return (1)
end

/*
** Determine whether the table we are working is APL or DOL.
**
** The bits 0x2000 (8192) and 0x0 (0) represent allpages
** lock scheme. The value of 0 indicates that no lock
** scheme is specified (old style tables), so they only
** support allpages locking.
**
** The bits 0x4000 (16384) and 0x8000 (32768) represent
** DOL tables respectively datapages only and datarows
** lock schemes.
**
** The value of 57344 is 8192+16384+32768.
*/
select @sysstat2 = (sysstat2 & 57344)
	from sysobjects
	where id = @objid

if (@sysstat2  = 8192 or @sysstat2 = 0)
	select @is_table_APL = 1
else
	select @is_table_APL = 0

/* 
** In preparation for the system catalogs and in-memory cache updates
** check each option for the valid option specifications and raise 
** errors if necessary. 
**
** option			catalog		column 		size
** ------------------------------------------------------------------
** concurrency_opt_threshold	systabstats	conopt_thld	int16
** identity_gap			sysindexes	identitygap	int32
** reservepagegap		sysindexes	res_page_gap	int16
** fillfactor			sysindexes	fill_factor	int16
** max_rows_per_page		sysindexes	maxrowsperpage	int16
** exp_row_size			sysindexes	exp_rowsize	int16
** optimistic_index_lock	sysobjects	sysstat2	int32
** dealloc_first_txtpg		sysobjects	sysstat2	int32
**
** option			domain validity checks made herein
**				for @optvalue
** -----------------------------------------------------------------------
** concurrency_opt_threshold	>= -1 and <= 32767
** identity_gap			>= 0
** reservepagegap		>= 0 and <= 255
** fillfactor			>= 0 and <= 100
** max_rows_per_page		none, instead via dbcc chgindcachedvalue()
** exp_row_size			none, instead via dbcc chgindcachedvalue()
** optimistic_index_lock	0,1 (mode off / mode on)
** dealloc_first_txtpg		0,1 (mode off / mode on) or NULL.
** identity_burn_max		none, instead via dbcc set_identity_burn_max()
**
** When a domain check passes we assign @optvalue to an int16 or int32
** local variable respectively @newlimit16 or @newlimit32.  If the new
** limit and the current value (@curlimit16 or @curlimit32) are the same
** we consider this sproc invocation being a NOOP.
**
** When the above checks pass below is what we do for the options :
**
**	reservepagegap,
**	max_rows_per_page,
**	fillfactor,
**	identity_gap,
**	concurrency_opt_threshold and
**	dealloc_first_txtpg.
**
** 1)	We start transaction rs_logexec
**
** 2)	We update sysindexes catalog and the in-memory cache thru
**	dbcc chgindcachedvalue().
**
**	We do NOT update sysindexes for option exp_row_size since
**	dbcc chgindcachedvalue() will begin a xact, update sysindexes
**	and then refresh the in-memory cache.  Notice that during the
**	checks phase for this option we may need to update sysindexes
**	if the column had a null value and this MUST not be done under
**	a transaction (here rs_logexec).  See comment further down for
**	the reason.  For that same reason for this option we only want
**	to execute:
**		begin tran rs_logexec
**		dbcc chgindcachedvalue("exp_row_size",...)
**		commit tran
**
**	We only update systabstats catalog for option 
**	concurrency_opt_threshold because this is a property of the
**	table required by the query optimizer and should thus be
**	derived from systabstats.
**
** 3)	We logexec() this sproc replication execution.
**
** 4)	We commit transaction rs_logexec
**
**	For the optimistic_index_lock option we update sysobjects and
**	(un)set the status 02_OPT_INDEX_LOCK in sysstat2. The command
**	dbcc tune() is used to update the in cache structures. The
**	exec of the stored procedure sp_chgattribute for this option
**	is not replicated on purpose. Doing this could seriously
**	impact performance on the replicated server if the workload on
**	the replicated server does not warrant this property.
**
**	For the dealloc_first_txtpg option we update sysobjects and
**	(un)set the status 02_DEALLOC_FIRST_TXTPG in sysstat2. To
**	handle all the additional logic for this option we call an
**	internal stored procedure sp_optimal_text_space. The return
**	status is checked. 
*/

if (@optname = "dealloc_first_txtpg")
begin
	exec @retstatus = sp_optimal_text_space @objname, 
						@optname,
						@optvalue

	if (@retstatus != 0)
	begin
		/*
		** No need to raise an error, this is already
		** done by sp_optimal_text_space.
		*/
		return (@retstatus)
	end
end

if (@optname = "optimistic_index_lock")
begin
	select @dbname = db_name()

	select @dbid = dbid from master.dbo.sysdatabases
		where name = @dbname
	
	/* 
	** This option is only applicable to APL tables.
	*/
	if (@is_table_APL = 0)
	begin
		/*
		** 18983, "The attribute '%1!' is not applicable
		**         to tables with datarow or datapages lock scheme."
		*/
		raiserror 18983, @optname
		return (1)
	end

	if (@optvalue not in (0, 1))
	begin
		/*
		** 18985, "The value for attribute '%1!' must be
		**         either 0 and 1"
		*/
		raiserror 18985, @optname
		return (1)
	end

	/* server defined constant for optimistic index lock */
	select @opt_ind_lock = 268435456

	begin transaction chg_opt_ind_lock

	if (@optvalue = 1)
	begin
		update sysobjects
		set sysstat2 = sysstat2 | @opt_ind_lock
		where name = @objname
	end
	else
	begin
		update sysobjects
		set sysstat2 = sysstat2 & ~@opt_ind_lock
		where name = @objname
	end

	if (@@error != 0)
	begin
		rollback tran

		/*
        	** "The attribute specified '%1!' could 
		**  not be set for the object. "
        	*/
        	raiserror 18987, @optname

		return(1)
	end

	select @newoptvalue = convert(varchar(10), @optvalue)

	dbcc tune(@optname, @dbid, @objname, @newoptvalue)

	if (@@error != 0)
	begin
		rollback tran

		/*
        	** "The attribute specified '%1!' could 
		**  not be set for the object. "
        	*/
        	raiserror 18987, @optname
        	return (1)
	end

	commit transaction

	/*
	** We don't want the exec of sp_chgattribute to
	** be replicated for this option (see comment above
	** where we comment each option).
	*/
	goto proc_end
end

/* 
** From here the third argument is set to the indexid.
*/
select @dbcc_arg_3 = @indid

if @optname = "concurrency_opt_threshold"
begin
	/* 
	** This option is only applicable to DOL tables.
	*/
	if (@is_table_APL = 1)
	begin
		/*
		** 18571, "The attribute '%1!' is not applicable
		**         to tables with allpages lock scheme."
		*/
		raiserror 18571, @optname
		return (1)
	end

	if (@optvalue < -1 or @optvalue > 32767)
	begin
		/*
		** 18612, "Invalid value specified for option '%1!'. 
		**         Valid range of values is %2! to %3!."
		*/
		raiserror 18612, @optname, -1, 32767
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = conopt_thld 
		from systabstats
 		where id = @objid and indid = 0

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if @optname = "reservepagegap"
begin
	/* 
	** Change required in the reservepagegap value of the table/index. 
	** The third argument of dbcc chgindcachedvalue() is index id.
	** The maximum value for reservepagegap set to 255. The only
	** reason for choosing this limit is that number of pages in 
	** an allocation unit is 256. 
	*/
	if (@optvalue < 0 or @optvalue > 255)
	begin
		/*
		** 18572, "The value for attribute '%1!' must be
		**         between %2! and %3!."
		*/
		raiserror 18572, @optname, 0, 255
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = res_page_gap 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if @optname = "identity_gap"
begin
	/* 
	** Raise error if negative value is specified for identity gap.
	*/
	if (@optvalue < 0)
	begin
		/* 
		** 18840, "The value specified for identity gap %1! is not
		**         legal; identity gap must be greater than or equal
		**         to 0."
		*/
		raiserror 18840, @optvalue
		return (1)
	end

	select @newlimit32 = @optvalue

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit32 = identitygap
		from sysindexes
		where id = @objid and indid = @indid

	if (@curlimit32 = @newlimit32)
		goto proc_end
end
else if @optname = "exp_row_size"
begin
	/*
	** Domain validity checks are done by dbcc chgindcachedvalue().
	*/
	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = exp_rowsize 
		from sysindexes
		where id = @objid and indid = @indid 

	if (@curlimit16 = @newlimit16)
		goto proc_end

	/* 
	** Set the exp_row_size value of the table/index. 
	**
	** The actual value set in sysindexes table for this table must
	** include the row overhead, which depends on the number of variable
	** length columns in the table. Since this attribute is applicable 
	** only for tables (not indexes) we are using the third argument 
	** of dbcc chgindcachedvalue() to pass the the number of variable 
	** length columns in the table. For this atribute, the sysindexes 
	** table will be updated by dbcc chgindcachedvalue(). This is because 
	** it is easier to calculate the overhead in the server than doing 
	** it here.
	**
	** Get the number of variable length columns the table has. 
	** This is determined by counting the number of columns that have
	** offset < 0. If a column is nullable, its offset will be < 0.
	**
	** This is required to determine the row overhead. 
	*/
	select @varcol_count = count(*)
		from syscolumns
		where id = @objid and offset < 0  

 	select @dbcc_arg_3 = @varcol_count

	/*
	** Update sysindexes and set the exp_row_size value to 0
	** if the column still has a null value. This is to facilitate
	** update to exp_row_size through sysindexes manager later
	** (thru dbcc chgindcachedvalue). This can be removed if we
	** are sure that exp_row_size can never be null. An important
	** point to note is this update is _not_ done within the scope
	** of the transaction which calls dbcc chgindcachedvalue and
	** will not rollback in the event of any failure after this
	** transaction. We don't want to call dbcc chgindcachedvalue
	** in the same transaction as this update, due to some in-memory
	** change rollback requirements (DES) for exp_rowsize.
	*/
	update sysindexes 
		set exp_rowsize = 0
		where id = @objid and indid = @indid and exp_rowsize is null
end
else if @optname = "fillfactor"
begin
	/* 
	** Change the fill_factor value of the table/index. 
	*/
	if (@optvalue < 0 or @optvalue > 100)
	begin
		/*
		** 18572, "The value for attribute '%1!' must be
		**         between %2! and %3!."
		*/
		raiserror 18572, @optname, 0, 100
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = fill_factor 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if (@optname = "max_rows_per_page")
begin
	/*
	** Domain validity checks are done by dbcc chgindcachedvalue().
	*/
	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = maxrowsperpage 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end 

/* create a temp table for use by identity_burn_max */
create table #maxid(id numeric(38,0) null)

/* 
** Now, do the actual work of updating system catalogs and the in-memory cache.
*/

/*
** IMPORTANT: The name rs_logexec is significant and is used by 
**            Replication Server
*/
begin transaction rs_logexec
 
if (@optname not in (
	"exp_row_size", 
     	"dealloc_first_txtpg",
	"identity_burn_max",
	"identity_burn_max_force"  -- RobV
	))
begin
	if (@optname = "reservepagegap")
 
 		update sysindexes
 			set res_page_gap = @newlimit16
 			where id = @objid
 			  and indid = @indid
 
 	else if (@optname = "max_rows_per_page")
 
 		update sysindexes
 			set maxrowsperpage = @newlimit16
 			where id = @objid
 			  and indid = @indid
 
 	else if (@optname = "fillfactor")
 
 		update sysindexes
 			set fill_factor = @newlimit16
 			where id = @objid
 			  and indid = @indid

	else if (@optname = "concurrency_opt_threshold")

		update systabstats
			set conopt_thld = @newlimit16
			where id = @objid
			  and indid = 0
 
	else if (@optname = "identity_gap")

		update sysindexes
			set identitygap = @newlimit32
			where id = @objid
			  and indid = @indid

 	/* else do_nothing */
 
 	/* If there was an error, @@error will be non-zero */
 	if @@error != 0
 	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
 		return(1)
 	end
end

if (@optname not in 
	("concurrency_opt_threshold", 
	 "dealloc_first_txtpg", 
	 "identity_burn_max",
	 "identity_burn_max_force"  -- RobV
	)) 
begin
	/*
	** Now, update the in-memory value of the parameter and check
	** whether it succeeded.
	*/
	if (@optname = "identity_gap")
		select @dbcc_arg_4 = @newlimit32
	else
		select @dbcc_arg_4 = convert(int, @newlimit16)

	dbcc chgindcachedvalue(@optname, @tabname, @dbcc_arg_3, @dbcc_arg_4)
	 
 	/* If there was an error, @@error will be non-zero */
	if @@error != 0
	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
		return(1)
	end
end

if (@optname in ("identity_burn_max", "identity_burn_max_force"))  -- RobV
begin
	if (@optvalue2 is NULL or @optvalue2 = '')
	begin
		rollback transaction

		/*
		** 19115, A value must be specified for attribute '%1!'.
		*/
		raiserror 19115, @optname 
		return (1)
	end

	/* get the name of identity colum from syscolumns */
	select @colname = name from syscolumns
		where id = object_id(@objname)
		and (status & 128 != 0)

	/* Table does not have identity column */
	if (@colname is NULL)
	begin
		rollback transaction

		/*
		** 19116, Table '%1!' does not have an identity column.
		*/
		raiserror 19116, @objname
		return(1)
	end

	/* When one user wants to set the identity value, other users
	** may have access to the same table. Possible inserts from
	** others will affect the max value of the identity column
	** we need for this option. To prevent from this, we will
	** lock the table using an exclusive lock.
	*/
	select @sqltext = "lock table " + @objname 
			  + " in exclusive mode wait 5"
	execute (@sqltext)

	if (@@error != 0)
	begin
		rollback transaction

		/* 
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/ 
		raiserror 18573, @optname, @objname
	end

	/* Make sure we do have EX_TAB lock acquired on the table */
	select @dbname = db_name()
	select @dbid = db_id()

	if not exists (select 1 from master..syslocks
			where id = object_id(@objname)
			and dbid = @dbid 
      			and (type & 1) != 0
			and spid = @@spid)
        begin
                rollback transaction

                /*
                ** 19118, "Object '%1!' is currently being accessed by other
                ** users. Failed to update attribute '%2!'."
                */
                raiserror 19118, @objname, @optname
                return(1)
        end
	

	select @new_idtval = convert(numeric(38,0), @optvalue2)

        if (@optname = "identity_burn_max")  -- RobV
        begin                                -- RobV
	   /* get the current max identity value in the table */
	   select @sqltext = "insert #maxid select "
			     + "max(" + @colname + ") from " + @objname
	   execute (@sqltext) 

	   if (@@error != 0)
	   begin
		   rollback transaction

		   /*
		   ** 18573, "Failed to update attribute '%1!' for object '%2!'."
		   */
		   raiserror 18573, @optname, @objname
	   end

	   /*
	   ** When a user wants to set a value less than 
	   ** the current max identity value, it may cause a duplicate
	   ** with the existing identity value in the table. This is 
	   ** therefore only allowed when the table has no rows.
	   */
	   select @curr_max_idtval = id from #maxid
	   
	   if (@curr_max_idtval > 0) and 
	       (@new_idtval < @curr_max_idtval)
	   begin
		   rollback transaction

		   /*
		   ** 19117, "The value %1! for '%2!' attribute must be 
		   ** greater than or equal to the current max identity 
		   ** value %3!."
		   */
		   raiserror 19117, @new_idtval, @optname, @curr_max_idtval 
		   return(1)
	   end
        end    -- RobV
	-- RobV: else: maybe print a message about the above validation not being performed

	dbcc set_identity_burn_max(@dbname, @objname, @optvalue2)

	/* If there was an error, @@error will be non-zero */
	if (@@error != 0)
	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
		return(1)
	end
end

/*
** Write the log record to replicate this invocation 
** of the stored procedure.
*/
if (logexec() != 1)
begin
	rollback transaction

	/*
	** 17756, "The execution of the stored procedure '%1!'
	** 	   in database '%2!' was aborted because there
	** 	   was an error in writing the replication log
	**	   record."
	*/
	select @dbname = db_name()
	raiserror 17756, "sp_chgattribute", @dbname
	return(1)
end
 
/* If all was successful, now commit the changes */
commit transaction
 

proc_end:
/*
** We are done. 
** 18122, "'%1!' attribute of object '%2!' changed to %3!."
*/
exec sp_getmessage 18122, @msg output

if (@optname in ("identity_burn_max", "identity_burn_max_force"))  -- RobV
	print @msg,@optname, @objname,@optvalue2
else
	print @msg,@optname, @objname,@optvalue

return (0)
go 

------ RobV: modified version of sp_chgattribute (12.5) ends here ------------------

------ RobV: modified version of sp_chgattribute (15.0) starts here ------------------
/*
** Messages for "sp_chgattribute"
**
** 17260, "Can't run %1! from within a transaction."
** 17460, "Object must be in the current database."
** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
** 17760, "%1!' is a system table. Cannot use '%2!' on system tables."
** 17782, "You do not own a table, column or index of that name in the current database."
** 18121, "Unrecognized change attribute option."
** 18571, "The attribute '%1!' is not applicable to tables with allpages lock scheme."
** 18572, "The value for attribute '%1!' must be between %2! and %3!."
** 18573, "Failed to update attribute '%1!' for object '%2!'."
** 18612, "Invalid value specified for option '%1!'. Valid range of values is %2! to %3!."
** 18613, "The attribute '%1!' is applicable to tables only."
** 18840, "The value specified for identity gap %1! is not legal; identity gap must be greater than or equal to 0."
** 18983, "The '%1!' attribute is not applicable to tables with datarow or datapage lock schemes."
** 18985, "The value for '%1!' attribute must be either 0 or 1."
** 18987, "The '%1!' attribute could not be set to the specified value for the object."
** 19115, "A value must be specified for attribute '%1!'."
** 19116, "Table '%1!' does not have an identity column."
** 19117, "The value %1! for '%2!' attribute must be greater than or equal to the current maximum identity value %3!."
** 19118, "Object '%1!' is currently being accessed by other users. Failed to update attribute '%2!'."
*/
/*
** For the option dealloc_first_txtpg we use the internal procedure sp_optimal_text_space
** to perform the necessary checks/updates. This procedure could raise additional
** messages.
*/

create procedure sp_chgattribute150  -- RobV: this procedure will be renamed later
@objname varchar(767),                   /* table or index name */
@optname varchar(30),
@optvalue int,
@optvalue2 varchar(38) = NULL		/* Current max digits for numeric */
as

declare @objid int                      /* object id of the table */
declare @indid smallint                 /* table's index id  */
declare @msg varchar(1024)
declare @dbname varchar(255)
declare @dbid	int			/* database id */
declare @tabname varchar(255)            /* name of table */
declare @indname varchar(255)            /* name of index */
declare @varcol_count smallint		/* Number of variable length columns */
declare @sysstat2 int                   /* status bits of the table */
      , @sysstat  int	   		/* objstat from sysobjects*/
      , @objtype  varchar(2)		/* Object's type */
declare @is_table_APL int               /* flag to distinguish APL and DOl tables */
declare @dbcc_arg_3 smallint		/* third argument to dbcc command. 
					** This must be of type smallint.
					*/
declare @newoptvalue varchar(10)	/* Used for converting the optvalue */
declare @opt_ind_lock int		/* server status for optimistic index lock */
declare @curlimit16 smallint		/* current value for int16 column */
declare @newlimit16 smallint		/* new value for int16 column */
declare @curlimit32 int			/* current value for int32 column */
declare @newlimit32 int			/* new value for int32 column */
declare @dbcc_arg_4 int			/* forth argument to dbcc command. 
					** This must be of type int.
					*/
declare @retstatus int
declare @curr_max_idtval numeric(38, 0)	/* current max id value in the table. */
declare @new_idtval numeric(38,0)	/* numeric number of input optvalue2 */
declare @colname varchar(255)		/* name of identity column */
declare @sqltext varchar(600)		/* tmp holder for sqltext */

/* If we're in a transaction, disallow this */
if @@trancount > 0
begin
	/*
	** 17260, "Can't run %1! from within a transaction."
	*/
	raiserror 17260, "sp_chgattribute"
	return (1)
end
else
begin
        set chained off
end

set transaction isolation level 1

if @objname like "%.%.%"
begin
	/*
	** 17460, "Object must be in the current database."
	*/
	raiserror 17460
	return (1)
end

if not exists (
	select * from master.dbo.spt_values 
		 where type = "H"
		 and  name = @optname)
begin
	/*
	** 18121, "Unrecognized change attribute option."
	*/
	raiserror 18121
	return (1)
end

if @objname like "%.%"
begin
	/*
	** Attributes 'exp_row_size' and 'concurrency_opt_threshold' apply 
	** only to a table and not to an index 
	*/	
	if (@optname = "concurrency_opt_threshold" or 
	    @optname = "exp_row_size" or
	    @optname = "optimistic_index_lock")
	begin
		/*
		** 18613, "The attribute '%1!' is applicable to tables only."
		*/
		raiserror 18613, @optname
		return (1)
	end	
		
	/*
	**  Get the table name and index name out.
	*/
	select @tabname = substring(@objname, 1, charindex(".", @objname) - 1)
	select @indname = substring(@objname, charindex(".", @objname) + 1, 511)

	select @objid = si.id, @indid = si.indid, @objtype = so.type
		from sysobjects so, sysindexes si
		where 	si.id = so.id
			and so.name = @tabname
			and si.name = @indname
			and si.indid != 0
			and so.type in ('U', 'S')	-- user / system tables
			and so.uid = user_id()
end
else
begin
	select @tabname = @objname
	select @objid = si.id, @indid=si.indid, @objtype = so.type
		from sysobjects so, sysindexes si
		where 	so.name = @tabname
			and si.id = so.id
			and si.indid in (0,1)
			and so.type in ('U', 'S')	-- user / system tables
			and so.uid = user_id()
end
/*
**  If @objid is still NULL then that means it isn't a 
**  table/index name.  
*/
if (@objid is null)
begin
	/*
	** 17782, "You do not own a table, column or index of that name in 
	** the current database."
	*/
	raiserror 17782
	return (1)
end

/*
** Determine whether the table we are working is APL or DOL.
**
** The bits 0x2000 (8192) and 0x0 (0) represent allpages
** lock scheme. The value of 0 indicates that no lock
** scheme is specified (old style tables), so they only
** support allpages locking.
**
** The bits 0x4000 (16384) and 0x8000 (32768) represent
** DOL tables respectively datapages only and datarows
** lock schemes.
**
** The value of 57344 is 8192+16384+32768.
**
** sysstat: 0x400 (1024) is for fake tables.
*/
select @sysstat2 = (sysstat2 & 57344)
	, @sysstat = (sysstat & 1024)	-- Eliminate fake catalogs
from sysobjects
where id = @objid

if (@sysstat2  = 8192 or @sysstat2 = 0)
	select @is_table_APL = 1
else
	select @is_table_APL = 0

if (@sysstat = 1024)
begin
	/*
	** Do not allow executing this for fake system catalogs.
	** It's meaningless and might cause unnecessary side-effects.
	** 18613, "The attribute '%1!' is applicable to tables only."
	*/
	raiserror 18613, @optname
	return (2)
end

/*
** Restrict use of this interface on system catalogs to only those
** options that control space-management properties. We don't want
** users to accidentally define, say, concurrency_opt_threshold 
** and cause unforeseen locking behaviour changes for system
** catalogs.
*/
if (    (@objtype = 'S')
     and @optname NOT IN ("concurrency_opt_threshold", "exp_row_size"))
begin

	select @msg = "sp_chgattribute " + @optname
	raiserror 17760, @tabname, @msg
	return (1)
end


/* 
** In preparation for the system catalogs and in-memory cache updates
** check each option for the valid option specifications and raise 
** errors if necessary. 
**
** option			catalog		column 		size
** ------------------------------------------------------------------
** concurrency_opt_threshold	systabstats	conopt_thld	int16
** identity_gap			sysindexes	identitygap	int32
** reservepagegap		sysindexes	res_page_gap	int16
** fillfactor			sysindexes	fill_factor	int16
** max_rows_per_page		sysindexes	maxrowsperpage	int16
** exp_row_size			sysindexes	exp_rowsize	int16
** optimistic_index_lock	sysobjects	sysstat2	int32
** dealloc_first_txtpg		sysobjects	sysstat2	int32
**
** option			domain validity checks made herein
**				for @optvalue
** -----------------------------------------------------------------------
** concurrency_opt_threshold	>= -1 and <= 32767
** identity_gap			>= 0
** reservepagegap		>= 0 and <= 255
** fillfactor			>= 0 and <= 100
** max_rows_per_page		none, instead via dbcc chgindcachedvalue()
** exp_row_size			none, instead via dbcc chgindcachedvalue()
** optimistic_index_lock	0,1 (mode off / mode on)
** dealloc_first_txtpg		0,1 (mode off / mode on) or NULL.
** identity_burn_max		none, instead via dbcc set_identity_burn_max()
**
** When a domain check passes we assign @optvalue to an int16 or int32
** local variable respectively @newlimit16 or @newlimit32.  If the new
** limit and the current value (@curlimit16 or @curlimit32) are the same
** we consider this sproc invocation being a NOOP.
**
** When the above checks pass below is what we do for the options :
**
**	reservepagegap,
**	max_rows_per_page,
**	fillfactor,
**	identity_gap,
**	concurrency_opt_threshold and
**	dealloc_first_txtpg.
**
** 1)	We start transaction rs_logexec
**
** 2)	We update sysindexes catalog and the in-memory cache thru
**	dbcc chgindcachedvalue().
**
**	We do NOT update sysindexes for option exp_row_size since
**	dbcc chgindcachedvalue() will begin a xact, update sysindexes
**	and then refresh the in-memory cache.  Notice that during the
**	checks phase for this option we may need to update sysindexes
**	if the column had a null value and this MUST not be done under
**	a transaction (here rs_logexec).  See comment further down for
**	the reason.  For that same reason for this option we only want
**	to execute:
**		begin tran rs_logexec
**		dbcc chgindcachedvalue("exp_row_size",...)
**		commit tran
**
**	For concurrency_opt_threshold, we update systabstats catalog and
**	the in-memory cache in pdes thru dbcc chgindcachedvalue().
**
** 3)	We logexec() this sproc replication execution.
**
** 4)	We commit transaction rs_logexec
**
**	For the optimistic_index_lock option we update sysobjects and
**	(un)set the status 02_OPT_INDEX_LOCK in sysstat2. The command
**	dbcc tune() is used to update the in cache structures. The
**	exec of the stored procedure sp_chgattribute for this option
**	is not replicated on purpose. Doing this could seriously
**	impact performance on the replicated server if the workload on
**	the replicated server does not warrant this property.
**
**	For the dealloc_first_txtpg option we update sysobjects and
**	(un)set the status 02_DEALLOC_FIRST_TXTPG in sysstat2. To
**	handle all the additional logic for this option we call an
**	internal stored procedure sp_optimal_text_space. The return
**	status is checked. 
**
** NOTE: All updates to sysindexes are done by forcing the index access via
** 'csysindexes' so that it's clear that only one row is being updated.
** (Multi-row updates to sysindexes are prohibited.)
*/

if (@optname = "dealloc_first_txtpg")
begin
	exec @retstatus = sp_optimal_text_space @objname, 
						@optname,
						@optvalue

	if (@retstatus != 0)
	begin
		/*
		** No need to raise an error, this is already
		** done by sp_optimal_text_space.
		*/
		return (@retstatus)
	end
end

if (@optname = "optimistic_index_lock")
begin
	select @dbname = db_name()

	select @dbid = dbid from master.dbo.sysdatabases
		where name = @dbname
	
	/* 
	** This option is only applicable to APL tables.
	*/
	if (@is_table_APL = 0)
	begin
		/*
		** 18983, "The attribute '%1!' is not applicable
		**         to tables with datarow or datapages lock scheme."
		*/
		raiserror 18983, @optname
		return (1)
	end

	if (@optvalue not in (0, 1))
	begin
		/*
		** 18985, "The value for attribute '%1!' must be
		**         either 0 and 1"
		*/
		raiserror 18985, @optname
		return (1)
	end

	/* server defined constant for optimistic index lock */
	select @opt_ind_lock = 268435456

	begin transaction chg_opt_ind_lock

	if (@optvalue = 1)
	begin
		update sysobjects
		set sysstat2 = sysstat2 | @opt_ind_lock
		where name = @objname
	end
	else
	begin
		update sysobjects
		set sysstat2 = sysstat2 & ~@opt_ind_lock
		where name = @objname
	end

	if (@@error != 0)
	begin
		rollback tran

		/*
        	** "The attribute specified '%1!' could 
		**  not be set for the object. "
        	*/
        	raiserror 18987, @optname

		return(1)
	end

	select @newoptvalue = convert(varchar(10), @optvalue)

	dbcc tune(@optname, @dbid, @objname, @newoptvalue)

	if (@@error != 0)
	begin
		rollback tran

		/*
        	** "The attribute specified '%1!' could 
		**  not be set for the object. "
        	*/
        	raiserror 18987, @optname
        	return (1)
	end

	commit transaction

	/*
	** We don't want the exec of sp_chgattribute to
	** be replicated for this option (see comment above
	** where we comment each option).
	*/
	goto proc_end
end

/* 
** From here the third argument is set to the indexid.
*/
select @dbcc_arg_3 = @indid

if @optname = "concurrency_opt_threshold"
begin
	/* 
	** This option is only applicable to DOL tables.
	*/
	if (@is_table_APL = 1)
	begin
		/*
		** 18571, "The attribute '%1!' is not applicable
		**         to tables with allpages lock scheme."
		*/
		raiserror 18571, @optname
		return (1)
	end

	if (@optvalue < -1 or @optvalue > 32767)
	begin
		/*
		** 18612, "Invalid value specified for option '%1!'. 
		**         Valid range of values is %2! to %3!."
		*/
		raiserror 18612, @optname, -1, 32767
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = conopt_thld 
		from systabstats
 		where id = @objid and indid = 0

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if @optname = "reservepagegap"
begin
	/* 
	** Change required in the reservepagegap value of the table/index. 
	** The third argument of dbcc chgindcachedvalue() is index id.
	** The maximum value for reservepagegap set to 255. The only
	** reason for choosing this limit is that number of pages in 
	** an allocation unit is 256. 
	*/
	if (@optvalue < 0 or @optvalue > 255)
	begin
		/*
		** 18572, "The value for attribute '%1!' must be
		**         between %2! and %3!."
		*/
		raiserror 18572, @optname, 0, 255
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = res_page_gap 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if @optname = "identity_gap"
begin
	/* 
	** Raise error if negative value is specified for identity gap.
	*/
	if (@optvalue < 0)
	begin
		/* 
		** 18840, "The value specified for identity gap %1! is not
		**         legal; identity gap must be greater than or equal
		**         to 0."
		*/
		raiserror 18840, @optvalue
		return (1)
	end

	select @newlimit32 = @optvalue

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit32 = identitygap
		from sysindexes
		where id = @objid and indid = @indid

	if (@curlimit32 = @newlimit32)
		goto proc_end
end
else if @optname = "exp_row_size"
begin
	/*
	** Domain validity checks are done by dbcc chgindcachedvalue().
	*/
	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = exp_rowsize 
		from sysindexes
		where id = @objid and indid = @indid 

	if (@curlimit16 = @newlimit16)
		goto proc_end

	/* 
	** Set the exp_row_size value of the table/index. 
	**
	** The actual value set in sysindexes table for this table must
	** include the row overhead, which depends on the number of variable
	** length columns in the table. Since this attribute is applicable 
	** only for tables (not indexes) we are using the third argument 
	** of dbcc chgindcachedvalue() to pass the the number of variable 
	** length columns in the table. For this atribute, the sysindexes 
	** table will be updated by dbcc chgindcachedvalue(). This is because 
	** it is easier to calculate the overhead in the server than doing 
	** it here.
	**
	** Get the number of variable length columns the table has. 
	** This is determined by counting the number of columns that have
	** offset < 0. If a column is nullable, its offset will be < 0.
	**
	** This is required to determine the row overhead. 
	*/
	select @varcol_count = count(*)
		from syscolumns
		where id = @objid and offset < 0  

 	select @dbcc_arg_3 = @varcol_count

	/*
	** Update sysindexes and set the exp_row_size value to 0
	** if the column still has a null value. This is to facilitate
	** update to exp_row_size through sysindexes manager later
	** (thru dbcc chgindcachedvalue). This can be removed if we
	** are sure that exp_row_size can never be null. An important
	** point to note is this update is _not_ done within the scope
	** of the transaction which calls dbcc chgindcachedvalue and
	** will not rollback in the event of any failure after this
	** transaction. We don't want to call dbcc chgindcachedvalue
	** in the same transaction as this update, due to some in-memory
	** change rollback requirements (DES) for exp_rowsize.
	*/
	update sysindexes 
	set exp_rowsize = 0
	where id = @objid and indid = @indid and exp_rowsize is null
	plan "(update (i_scan csysindexes sysindexes))"
end
else if @optname = "fillfactor"
begin
	/* 
	** Change the fill_factor value of the table/index. 
	*/
	if (@optvalue < 0 or @optvalue > 100)
	begin
		/*
		** 18572, "The value for attribute '%1!' must be
		**         between %2! and %3!."
		*/
		raiserror 18572, @optname, 0, 100
		return (1)
	end

	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = fill_factor 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end
else if (@optname = "max_rows_per_page")
begin
	/*
	** Domain validity checks are done by dbcc chgindcachedvalue().
	*/
	select @newlimit16 = convert(smallint, @optvalue)

	/*
	** Do nothing if the new specified and current values are the same.
	*/
	select @curlimit16 = maxrowsperpage 
		from sysindexes
 		where id = @objid and indid = @indid

	if (@curlimit16 = @newlimit16)
		goto proc_end
end 

/* create a temp table for use by identity_burn_max */
create table #maxid(id numeric(38,0) null)

/* 
** Now, do the actual work of updating system catalogs and the in-memory cache.
*/

/*
** IMPORTANT: The name rs_logexec is significant and is used by 
**            Replication Server
*/
begin transaction rs_logexec
 
if (@optname not in (
	"exp_row_size", 
     	"dealloc_first_txtpg",
	"identity_burn_max",
	"identity_burn_max_force"  -- RobV
	))
begin
	if (@optname = "reservepagegap")
 
 		update sysindexes
 		set res_page_gap = @newlimit16
 		where id = @objid and indid = @indid
		plan "(update (i_scan csysindexes sysindexes))"
 
 	else if (@optname = "max_rows_per_page")
 
 		update sysindexes
 		set maxrowsperpage = @newlimit16
 		where id = @objid and indid = @indid
		plan "(update (i_scan csysindexes sysindexes))"
 
 	else if (@optname = "fillfactor")
 
 		update sysindexes
 		set fill_factor = @newlimit16
 		where id = @objid and indid = @indid
		plan "(update (i_scan csysindexes sysindexes))"

	else if (@optname = "concurrency_opt_threshold")

		update systabstats
			set conopt_thld = @newlimit16
			where id = @objid
			  and indid = 0
 
	else if (@optname = "identity_gap")

		update sysindexes
		set identitygap = @newlimit32
		where id = @objid and indid = @indid
		plan "(update (i_scan csysindexes sysindexes))"

 	/* else do_nothing */
 
 	/* If there was an error, @@error will be non-zero */
 	if @@error != 0
 	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
 		return(1)
 	end
end

if (@optname not in 
	("dealloc_first_txtpg", 
	 "identity_burn_max",
	 "identity_burn_max_force"  -- RobV
    )) 
begin
	/*
	** Now, update the in-memory value of the parameter and check
	** whether it succeeded.
	*/
	if (@optname = "identity_gap")
		select @dbcc_arg_4 = @newlimit32
	else
		select @dbcc_arg_4 = convert(int, @newlimit16)

	dbcc chgindcachedvalue(@optname, @tabname, @dbcc_arg_3, @dbcc_arg_4)

	/* If there was an error, @@error will be non-zero */
	if @@error != 0
	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
		return(1)
	end
end

if (@optname in ("identity_burn_max", "identity_burn_max_force"))  -- RobV
begin
	if (@optvalue2 is NULL or @optvalue2 = '')
	begin
		rollback transaction

		/*
		** 19115, A value must be specified for attribute '%1!'.
		*/
		raiserror 19115, @optname 
		return (1)
	end

	/* get the name of identity colum from syscolumns */
	select @colname = name from syscolumns
		where id = object_id(@objname)
		and (status & 128 != 0)

	/* Table does not have identity column */
	if (@colname is NULL)
	begin
		rollback transaction

		/*
		** 19116, Table '%1!' does not have an identity column.
		*/
		raiserror 19116, @objname
		return(1)
	end

	/* When one user wants to set the identity value, other users
	** may have access to the same table. Possible inserts from
	** others will affect the max value of the identity column
	** we need for this option. To prevent from this, we will
	** lock the table using an exclusive lock.
	*/
	select @sqltext = "lock table " + @objname 
			  + " in exclusive mode wait 5"
	execute (@sqltext)

	if (@@error != 0)
	begin
		rollback transaction

		/* 
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/ 
		raiserror 18573, @optname, @objname
	end

	/* Make sure we do have EX_TAB lock acquired on the table */
	select @dbname = db_name()
	select @dbid = db_id()

	if not exists (select 1 from master..syslocks
			where id = object_id(@objname)
			and dbid = @dbid 
      			and (type & 1) != 0
			and spid = @@spid)
        begin
                rollback transaction

                /*
                ** 19118, "Object '%1!' is currently being accessed by other
                ** users. Failed to update attribute '%2!'."
                */
                raiserror 19118, @objname, @optname
                return(1)
        end
	

	select @new_idtval = convert(numeric(38,0), @optvalue2)

    if (@optname = "identity_burn_max")  -- RobV
    begin                                -- RobV
	/* get the current max identity value in the table */
	select @sqltext = "insert #maxid select "
			  + "max(" + @colname + ") from " + @objname
	execute (@sqltext) 

	if (@@error != 0)
	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
	end

	/*
	** When a user wants to set a value less than 
	** the current max identity value, it may cause a duplicate
	** with the existing identity value in the table. This is 
	** therefore only allowed when the table has no rows.
	*/
	select @curr_max_idtval = id from #maxid
 	
	if (@curr_max_idtval > 0) and 
	    (@new_idtval < @curr_max_idtval)
	begin
		rollback transaction

		/*
		** 19117, "The value %1! for '%2!' attribute must be 
		** greater than or equal to the current max identity 
		** value %3!."
		*/
		raiserror 19117, @new_idtval, @optname, @curr_max_idtval 
		return(1)
	end
    end    -- RobV
	-- RobV: else: maybe print a message about the above validation not being performed

	dbcc set_identity_burn_max(@dbname, @objname, @optvalue2)

	/* If there was an error, @@error will be non-zero */
	if (@@error != 0)
	begin
		rollback transaction

		/*
		** 18573, "Failed to update attribute '%1!' for object '%2!'."
		*/
		raiserror 18573, @optname, @objname
		return(1)
	end
end

/*
** Write the log record to replicate this invocation 
** of the stored procedure.
*/
if (logexec() != 1)
begin
	rollback transaction

	/*
	** 17756, "The execution of the stored procedure '%1!'
	** 	   in database '%2!' was aborted because there
	** 	   was an error in writing the replication log
	**	   record."
	*/
	select @dbname = db_name()
	raiserror 17756, "sp_chgattribute", @dbname
	return(1)
end
 
/* If all was successful, now commit the changes */
commit transaction
 

proc_end:
/*
** We are done. 
** 18122, "'%1!' attribute of object '%2!' changed to %3!."
*/
exec sp_getmessage 18122, @msg output

if (@optname in ("identity_burn_max", "identity_burn_max_force"))  -- RobV
	print @msg,@optname, @objname,@optvalue2
else
	print @msg,@optname, @objname,@optvalue

return (0)
go

------ RobV: modified version of sp_chgattribute (15.0) ends here ------------------

if license_enabled('ASE_PARTITIONS') is not NULL
begin
   -- we need to install the 15.0 version instead, so drop the 12.5 version
   drop proc sp_chgattribute

   --print "Dropping 12.5 version"
end
go

if license_enabled('ASE_PARTITIONS') is NULL
begin
   -- get rid of the 15.0 version
   drop proc sp_chgattribute150

   --print "Dropping 15.0 version"
end
else
begin
   -- rename to sp_chgattribute; the earlier created 12.5 version is already dropped
   exec sp_rename sp_chgattribute150, sp_chgattribute

   --print "Renaming 15.0 version"
end
go

sp_configure 'allow up', 0
go

-----------------------------------------------------------------

dump tran sybsystemprocs with truncate_only
go

print " "
print "Ready!"
print " "
go