SQL SERVER self-add column _mssql

DECLARE @Table_name varchar (60)

Set @Table_name = ';

If Exists (Select top 1 1 sysobjects
Where objectproperty (ID, ' tablehasidentity ') = 1
and upper (name) = Upper (@Table_name)
)
Select 1
Else Select 0

--or

If Exists (Select top 1 1-sysobjects so
Where so.xtype = ' U '
and upper (so.name) = Upper (@Table_name)
and Exists (Select top 1 1 syscolumns SC
Where sc.id = so.id
and ColumnProperty (sc.id, Sc.name, ' isidentity ') = 1
)
)
Select 1
Else Select 0

Determines whether the table has a self-add column (Identity column) and identifies the data from the added 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-added field name
Ident_current (So.name) Curr_value,--self-added field current value
IDENT_INCR (So.name) Incr_value,--Self-increasing field growth value
Ident_seed (So.name) Seed_value--Self-increasing 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 to check for its current identity value. The table name 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 CREATE TABLE or ALTER table statement), the current identity value is not corrected.

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

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

DBCC checkident Statements	identification Corrections 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 table's current identity value is less than the maximum identity value stored in the column, the maximum value in the identity column is used to reset it.
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 it 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 error message will be generated when the table is referenced later.

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:

• Executes the DBCC CHECKIDENT ('table_name', noreseed) to determine the current maximum value in the column, and then uses the DBCC CHECKIDENT (' table_name ', reseed, new_reseed_valueThe statement specifies the value as new_reseed_value.
  
  
• The new_reseed_value is set to a very small value to perform the DBCC checkident ('table_name', Reseed, New_reseed_value), and then run the DBCC checkident ('table_name', reseed).

Result set

The DBCC checkident returns the following result set (values may vary) regardless of whether any options are specified (for the table containing the identity column; The following example uses the jobs table of the pubs database):

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

Permissions

DBCC checkident permissions are granted by default to the table owner, thesysadmin fixed server role, and members of the db_owner fixed database role and are not transferable.

Example

A. Reset the current identity value if necessary

The following example resets the current identity value of the jobs table, if necessary.

Use the pubs go
DBCC checkident (jobs) Go

B. Reporting current identity values

The following example reports the current identity value in the jobs table, and does not correct it if the identity value is incorrect.

Use the pubs go
DBCC checkident (jobs, noreseed) go

C. Force the current identity value to 30

The following example forces the current identity value in the jobs table to 30.

  Use the pubs go DBCC checkident (jobs, reseed, the) go