Alter Identity Column Value in SQL Server

In my previous article I talked about creating identity column and insert data into Identity column. In this article I would like to talk about following topics:

1) Identity columns in a database
2) Current Identity Column value
3) Alter Identity Columns
1) Find all the Identity Columns in SQL Server:

SELECT Table_name,Column_Name
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE COLUMNPROPERTY(
OBJECT_ID(c.TABLE_NAME),
c.COLUMN_NAME,'IsIdentity') = 1

2) Find the Current Identity Column value
Now take any table name from the above list and call CHECKINDENT function.

Listing 1:
DBCC CHECKIDENT ('DOMAIN', NORESEED)
Output is:
Checking identity information: current identity value '7', current column value '7'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Listing 2:
DBCC CHECKIDENT ('CUSTOMER', NORESEED)
If you try to pass a table which doesn’t have the Identity column then it will return following error message.
Msg 7997, Level 16, State 1, Line 1'CUSTOMER' does not contain an identity column.

3) Alter Identity Column Value
Listing 1:
DBCC CHECKIDENT ('DOMAIN', RESEED,999)
Output is:
Checking identity information: current identity value '99', current column value '999'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So when you insert next value it will start 999+1.