Altering Identity Columns for Bidirectional TransasctionalReplication

Having 300 tables (all of them with identity columns) with
prepopulated data, I've come to a point where I have to alter all the
tables to have an identity conflict resolution system. In my case I
only have a publisher and a subscriber, hence I've opted to have the
publisher generate only even identities, and the subscriber will be
generating odd values.
I can't seem to find, besides using Enterprise Management (Design
Table Form), any other way to alter the existing identity step already
defined to the table.
Any ideas on how about to either alter the table (preferibly using
SQL), or perhaps a different approach to get the existing tables using
the new identity step (I would rather not have to export the data,
drop the tables, recreate them again, and reload the data once more)
Thank you,
James.
Here is how I ended up doing:
- Backup the original Database containing all the data:
- Create a new database - name it backup_db
- Destroy the orginal database and recreate the database only.
- Take the scripts that created the original database (if none is
available then export the scripts from the backup_db)
- Edit the script and alter all the IDENTITY(1,1) to the desired
seed and step.
- In the original db (now empty) create only the tables - don't
worry about the views, procedures and any other objects.
- Create a snapshot replication using the backup_db as the
publishing database: ensure that the published articles are marked to
only "Delete all the data in the existing table" - this will ensure
that the subscriber's tables are not destroyed (hence keeping your new
identity definition"
- Subscribe the original db to the publication and sync it.
- Once the replication has completed we now need to update all the
tables with an identity to reseed with the latest highest value - you
can do this by defining the following script (in my case, I need the
seed to be even):
BEGIN
DECLARE @.new_ident as int
SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
IF (@.new_ident % 2) <> 0
SET @.new_ident = @.new_ident + 1
DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
END
|||Small errata:

> BEGIN
DBCC CHECKIDENT('your_table', RESEED)
> DECLARE @.new_ident as int
> SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
> IF (@.new_ident % 2) <> 0
> SET @.new_ident = @.new_ident + 1
> DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
> END
Also, in order to generate all the scripts for each table you could
use a mail merge program with a list of your table names - this should
save time and user errors.