MS SQL – Change/reseed value on identity column

18 Sep

How to find current value on a identity column:

SELECT IDENT_SEED (TABLE_NAME) AS Seed,
IDENT_INCR (TABLE_NAME) AS Increment,
IDENT_CURRENT (TABLE_NAME) AS Current_Identity,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY (OBJECT_ID (TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’

How to change/reset a value on identity column:

DBCC CHECKIDENT(‘DATABASEX.dbo.TABLEX’, RESEED, Numberx)

 

Run this command to check current nr of identity columns in the database:

SELECT TABLE_NAME,
COLUMN_NAME,
IDENT_SEED (TABLE_NAME) AS Seed,
IDENT_INCR (TABLE_NAME) AS Increment,
IDENT_CURRENT (TABLE_NAME) AS Current_identity
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ‘dbo’
AND COLUMNPROPERTY (object_id (TABLE_NAME), COLUMN_NAME, ‘IsIdentity’) = 1
ORDER BY TABLE_NAME

 

Read more:

http://blog.sqlauthority.com/2007/04/23/sql-server-query-to-find-seed-values-increment-values-and-current-identity-column-value-of-the-table/

http://stackoverflow.com/questions/14445185/dbcc-checkident-reseed-is-new-value-required

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: