SQL Server auto-increment Column

Declare @ table_name varchar (60)

Set @ table_name = '';

If exists (select top 1 1 from sysobjects
Where objectproperty (ID, 'tablehasauth') = 1
And upper (name) = upper (@ table_name)
)
Select 1
Else select 0

-- Or

If exists (select top 1 1 from sysobjects so
Where so. xtype = 'U'
And upper (so. Name) = upper (@ table_name)
And exists (select top 1 1 from syscolumns SC
Where SC. ID = So. ID
And columnproperty (SC. ID, SC. Name, 'isidentity ') = 1
)
)
Select 1
Else select 0

Determine whether the table has an auto-increment column (identity column) and check the relevant data of the auto-increment column:

Declare @ table_name varchar (60)
Set @ table_name = '';

Declare @ table_name varchar (60)
Set @ table_name = 'zy _ cost_list ';
Select so. Name table_name, -- table name
SC. Name iden_column_name, -- auto-increment field name
Ident_current (so. Name) curr_value, -- current value of the auto-increment Field
Ident_incr (so. Name) incr_value, -- auto-increment field Growth Value
Ident_seed (so. Name) seed_value -- seed value of the auto-increment Field
From sysobjects so
Inner join syscolumns SC
On so. ID = SC. ID
And columnproperty (SC. ID, SC. Name, 'isidentity ') = 1
Where upper (so. Name) = upper (@ table_name)

Reference:

DBCC checkident

Check the current Identifier value of the specified table. If necessary, correct the Identifier value.

Syntax

DBCC checkident
('Table_name'
[,{Noreseed
| {Reseed [, New_reseed_value]}
}
]
)

Parameters

'Table_name'

Is the name of the table whose current identity value is to be checked. The table name must comply with the identifier rules. For more information, seeIdentifier. The specified table must contain an ID column.

Noreseed

Specifies that the current ID value should not be corrected.

Reseed

Specifies that the current ID value should be corrected.

New_reseed_value

Is the value to be used when the value is re-assigned in the ID column.

Note

If necessary, DBCC checkident will correct the current ID value of the column. However, if the ID column is created using the not for replication clause (in the create table or alter table statement), the current ID value is not corrected.

If the primary key or unique key constraint exists in the ID column, invalid ID information may cause error 2627.

The specific correction to the current Identifier value depends on the parameter specification.

DBCC checkident statement	Id correction
DBCC checkident ('Table_name', Noreseed)	The current ID value is not reset. DBCC checkident returns a report that specifies the current ID value and the expected id value.
DBCC checkident ('Table_name') Or DBCC checkident ('Table_name', Reseed)	If the current ID value of the table is smaller than the maximum id value stored in the column, reset it with the maximum value in the column.
DBCC checkident ('Table_name', Reseed,New_reseed_value)	The current value is setNew_reseed_value. If the row is not inserted into the table after the table is created, the first row inserted after DBCC checkident is executed will useNew_reseed_valueAs the identifier. Otherwise, the next inserted row usesNew_reseed_value+ 1. IfNew_reseed_valueThe value of is less than the maximum value in the identification column. When you reference the table later, error 2627 is generated.

The current ID value can be greater than the maximum value in the table. In this case, DBCC checkident does not automatically reset the current ID value. To reset the current ID value when the current ID value is greater than the maximum value in the column, use either of the following methods:

• Run DBCC checkident ('Table_name', Noreseed) to determine the current maximum value in the column, and then use DBCC checkident ('Table_name', Reseed,New_reseed_value) Statement to specify this valueNew_reseed_value.

• SetNew_reseed_valueTo execute DBCC checkident ('Table_name', Reseed,New_reseed_value), And then run DBCC checkident ('Table_name', Reseed ).

Result set

Whether or not any options are specified (for tables that contain the ID column; the following example usesPubsDatabaseJobsTable), DBCC checkident returns the following result set (the value may change ):

Checking identity information: Current identity value '14', current column value '14'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 

Permission

DBCC checkident permission is granted to the table owner by default,SysAdminFixed server roles andDb_ownerMembers of a fixed database role cannot be transferred.

Example A. Reset the current ID value if necessary

The following example is reset if necessary.JobsThe current Identifier value of the table.

 
Use pubsgodbcc checkident (jobs) Go
 

B. report the current ID value

Example reportJobsThe current ID value in the table. If the id value is incorrect, it is not corrected.

 
Use pubsgodbcc checkident (jobs, noreseed) Go
 

C. Force the current ID value to 30

Force the following exampleJobsThe current ID value in the table is 30.

 
Use pubsgodbcc checkident (jobs, reseed, 30) Go