SQL Server:我如何获得AUTO_INCREMENT状态

本文关键字：AUTO INCREMENT 状态 何获得 Server SQL | 更新日期: 2023-09-27 18:19:01

如果我在person表中有AUTO_INCREMENT列，从1开始，递增2现在，在表中添加一行后

我

ID|Fname|Lname
---------------
1 |check|check2
3 |check3|check4

如何在SQL查询中获得增量的状态我的意思是我想在SQL中获得现在的值和增量

在这个例子中，我想从查询中得到:

select 
    increment_value, increment_by 
from 
    INFORMATION_SCHEMA.COLUMNS/sys.columns 
where 
    tablename= 'Person'

我想得到的结果是3,2

如果人们被困在相同的情况下，那么

这里是我最后的代码

用c# 

    //check columns is auto incresment if yes = True or or not =False
string sql1 = string.Format("SELECT is_identity FROM sys.columns WHERE object_id = object_id('{0}') AND name = '{1}'","tablename","ColumnName");
string str = DoQueryWithReturn("db.mdf", sql1);
MessageBox.Show(str[0]);

//get the ident increment Seed = start value , INCR= how many it up , Current it what number right now, last row
sql1 = string.Format(" SELECT IDENT_SEED('{0}'),IDENT_INCR ('{0}'),IDENT_CURRENT('{0}') AS Identity_Seed ", "tablename");
str = DoQueryWithReturn("db.mdf", sql1);
if(str[0].Length!=0)
MessageBox.Show(str[0]+","+str[1]+","+str[2]);

SQL Server:我如何获得AUTO_INCREMENT状态

用于编辑检查列是否为auto_increment (identity);你可以参考这个问题

edit:检查种子值;你可以使用:

IDENT_SEED ( 'table_or_view' )

参见:IDENT_SEED

检查增量，可以使用:

IDENT_INCR ( 'table_or_view' )

参见:IDENT_INCR

要获得所需的结果，请尝试

DECLARE @table varchar(50) = 'Person'
SELECT is_identity, IDENT_SEED(@table) AS Seed,
IDENT_INCR(@table) AS Increment,
IDENT_CURRENT(@table) AS Current_Identity
FROM sys.columns
WHERE 
    object_id = object_id(@table)
    AND name = 'Id' -- column name

您可以使用以下语句检查列是否为IDENTITY列:

SELECT     
    name,
    is_identity
FROM 
    sys.columns 
WHERE 
    OBJECT_ID = OBJECT_ID('dbo.YourTableNameHere')
    AND name = 'ColumnNameHere'

要查找种子值、增量和最后使用的值，使用下面的语句:

SELECT 
    ColumnName = name,
    TableName = OBJECT_NAME(object_id),
    seed_value ,
    increment_value ,
    last_value 
FROM 
    sys.identity_columns
相关文章：