------------------------------------
/* Stored procedure to generate sequenced values for a column of table. */
------------------------------------
CREATE PROCEDURE renumber_identity_column
@tablename nvarchar(255),
@colname nvarchar(255),
@ident_seed int,
@ident_incr int
AS
DECLARE
@SQLString nvarchar(500)
SET @SQLString =
'DECLARE @intCounter int, @ident_incr int ' +
'SET @intCounter = ' + CAST(@ident_seed AS varchar) + ' ' +
'SET @ident_incr = ' + CAST(@ident_incr AS varchar) + ' ' +
'SET @intCounter = @intCounter - @ident_incr ' +
'UPDATE ' + @tablename + ' SET @intCounter = ' + @colname + ' = @intCounter + @ident_incr'
EXEC sp_executesql @SQLString
GO
------------------------------------
/*
After creating the stored procedure listed, this script should be executed
to get the identity column of a table renumbered.
Note: Undocumented features of SQL Server 2000 used, not tested with
the previous versions. Use it at your own risk.
*/
------------------------------------
/* Allow ad hoc update to system catalogs. */
EXEC sp_configure 'allow update', '1'
RECONFIGURE WITH OVERRIDE
GO
/* Reset identity property of the column */
DECLARE
@tablename varchar(128),
@colname varchar(128),
@tableid int,
@ident_seed int,
@ident_incr int
/* Name of the table with the identity column to be renumbered. */
SET @tablename = 'TableName'
SELECT @tableid = OBJECT_ID(@tablename)
SELECT @ident_seed = IDENT_SEED(@tablename)
SELECT @ident_incr = IDENT_INCR(@tablename)
/* Find the identity column. */
SELECT @colname = name FROM syscolumns
WHERE id = @tableid and (colstat & 1 <> 0)
/*
!!! Note: status is a computed column and update is not allowed.
colstat=1 when identity property is set for the column.
status tinyint
Bitmap used to describe a property of the column or the parameter:
0x08 = Column allows null values.
0x10 = ANSI padding was in effect when varchar or varbinary columns were added.
Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
0x40 = Parameter is an OUTPUT parameter.
0x80 = Column is an identity column.
update syscolumns
set status = status & ~cast(0x80 as tinyint)
where id = @tableid and name = @colname
*/
/*
below given an excerpt from the formula of the computed column 'status'.
...
case when ([colstat] & 1 <> 0)
then 128
else 0
end)))
thus, we conclude with the undocumented info;
colstat type smallint;
colstat=1 when identity is set.
*/
/* Reset identity. */
UPDATE syscolumns
SET colstat = colstat & ~CAST(0x01 AS smallint)
WHERE id = @tableid and name = @colname
EXEC renumber_identity_column @tablename, @colname, @ident_seed, @ident_incr
/* Set identity. */
UPDATE syscolumns
SET colstat = colstat | CAST(0x01 AS smallint)
WHERE id = @tableid and name = @colname
GO
EXEC sp_configure 'allow update', '0'
RECONFIGURE WITH OVERRIDE
GO
------------------------------------
|