Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve self-addition in SQL SERVER. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

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 1else 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 1else select 0

Determine whether there is a self-increment column (Identity column) in Table, and find out the data related to the self-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') = 1Where upper (so.name) = upper (@ Table_name)

Information quotation:

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

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. 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 reassigning values in the identity column.

Annotation

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.

If there is a primary key or unique key constraint on the identity column, invalid identification information may result in error message 2627.

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

DBCC CHECKIDENT ('table_name', NORESEED) does not reset the current identity value. DBCC CHECKIDENT returns a report that indicates the current identity value and the identity value it should have. 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 using the maximum value in the identity column. The current value of DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) is set to new_reseed_value. If no rows have been inserted into the table since it was created, the first row inserted after the DBCC CHECKIDENT is executed will be identified by new_reseed_value. 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, an error message 2627 will be generated when referencing the table 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 two methods:

Execute 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 the value as

New_reseed_value

. Set

New_reseed_value

Set to a very small value to execute DBCC CHECKIDENT (

'table_name'

, RESEED

New_reseed_value

And then run DBCC CHECKIDENT (

'table_name'

, RESEED.

Result set

Regardless of whether or not any option is specified (for a table that contains identity columns; the following example uses the jobs table of the pubs database), DBCC CHECKIDENT returns the following result set (values may vary):

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

DBCC CHECKIDENT permissions are granted by default to table owners, members of the sysadmin fixed server role, and members of the db_owner fixed database role and are not transferable.

Example A. If necessary, reset the current identity value

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

USE pubsGODBCC CHECKIDENT (jobs) GOB. Report current identity value

The following example reports the current identity value in the jobs table; if the identity value is incorrect, it is not corrected.

USE pubsGODBCC CHECKIDENT (jobs, NORESEED) GOC. Force the current identity value to 30

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

USE pubsGODBCC CHECKIDENT (jobs, RESEED, 30) GO above is how to achieve self-addition in the SQL SERVER shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.