For the past few days, I have been working on the new version of a database. The database was in development and had to go in production. Of course, the right structure and lookup tables data was in the development version of the database and the good data was in the old production database. Both had to be merged, and it was important to keep the same primary keys in both databases. There is a transition period during which both database will be in production, so the data has to be entered in the old and new database for that period. It was therefore important to make sure that the table IDENTs were identical in both databases so that new rows inserted on both sides would get the same IDs.

Here is a script that allows to make sure of this:

select kc1.table_name AS 'Table', kc1.column_name AS 'PK',
IDENT_CURRENT(kc1.CONSTRAINT_CATALOG+'.'+kc1.CONSTRAINT_SCHEMA+'.'+kc1.table_name) AS 'IDENT1',
IDENT_CURRENT(kc2.CONSTRAINT_CATALOG+'.'+kc2.CONSTRAINT_SCHEMA+'.'+kc2.table_name) AS 'IDENT2'
FROM <Old Database Name>.dbo.sysobjects so1
INNER JOIN <Old Database Name>.INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc1
    ON so1.name=kc1.table_name
INNER JOIN <New Database Name>.INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc2
    ON kc1.table_name=kc2.table_name AND kc1.column_name=kc2.column_name
INNER JOIN <New Database Name>.dbo.sysobjects so2
    ON so2.name=kc2.table_name
WHERE so1.xtype='U'
    AND OBJECTPROPERTY(OBJECT_ID(kc1.constraint_name), 'IsPrimaryKey') = 1
    AND so2.xtype='U'
    AND OBJECTPROPERTY(OBJECT_ID(kc2.constraint_name), 'IsPrimaryKey') = 1
    AND IDENT_CURRENT(kc1.CONSTRAINT_CATALOG+'.'+kc1.CONSTRAINT_SCHEMA+'.'+kc1.table_name) <> IDENT_CURRENT(kc2.CONSTRAINT_CATALOG+'.'+kc2.CONSTRAINT_SCHEMA+'.'+kc2.table_name)

In order to modify a table’s IDENT, it is possible to use the following command:

DBCC CHECKIDENT (<Table Name>, reseed, <IDENT Value>)

Leave a Reply

Your email address will not be published. Required fields are marked *