Methods for SQL SERVER self-add columns

DECLARE @Table_name varchar (60)

Set @Table_name = ';

If Exists (Select top 1 1 from sysobjects
Where objectproperty (ID, ' tablehasidentity ') = 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 if the table has a self-increment column (Identity column) and isolate the data from the 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,--Self-increment field name
Ident_current (So.name) Curr_value,--Self-increment field current value
IDENT_INCR (So.name) incr_value, self-increment field growth value
Ident_seed (So.name) Seed_value--Self-increment field seed value
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)

Data reference:

DBCC checkident

Checks the current identity value of the specified table and, if necessary, corrects the identity value.

Grammar
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. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. The specified table must contain an identity column.

Noreseed

Specifies that the current identity value should not be corrected.

Reseed

Specifies that the current identity value should be corrected.

New_reseed_value

is the value to use when the value is re-assigned in the identity column.

Comments

If necessary, DBCC Checkident corrects the current identity value of the column. However, if the identity column is created using the NOT for REPLICATION clause (in the Create TABLE or ALTER table statement), the current identity value is not corrected.

Invalid identity information can cause error message 2627 If there is a primary KEY or UNIQUE key constraint on the identity column.

The specific corrections made to the current identity value depend on the parameter specification.

DBCC checkident Statements
The identity corrections that were made
DBCC checkident (' table_name ', noreseed)
The current identity value is not reset. DBCC Checkident Returns a report that indicates the current identity value and the expected identity value.
DBCC checkident (' table_name ') or
DBCC checkident (' table_name ', reseed)
If the current identity value of the table is less than the maximum identity value stored in the column, it is reset with the maximum value in the identity column.
DBCC checkident (' table_name ', reseed, New_reseed_value)
The current value is set to New_reseed_value. If a row has not been inserted into the table since the table was created, the first row inserted after the DBCC checkident is executed uses new_reseed_value as the identity. Otherwise, the next inserted row will use New_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, a No. 2627-number error message is generated later when the table is referenced.

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

Execute DBCC CHECKIDENT (' table_name ', noreseed) to determine the current maximum value in the column, and then use the DBCC CHECKIDENT (' table_name ', RESEED, new_reseed_value) statement to The value is specified as New_reseed_value.
Set New_reseed_value to a very small value to perform DBCC CHECKIDENT (' table_name ', reseed, New_reseed_value), and then run DBCC CHECKIDENT (' table_name ', reseed).
Result set

Methods for adding columns to SQL SERVER