SQL Server : how I get AUTO_INCREMENT status

Question

If I have column with AUTO_INCREMENT in person table that was start with 1 and incremented by 2 now after I add a row in that table

I have

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

How in a SQL query can I get the status of the increment I mean I want to get in the SQL the value now and the increment by

In that example I want to get from the query like:

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

the result that I want to get is 3,2

if people is stuck with the same situation so

here my last code

in 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]);

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

For your edit to check if a column is auto_increment (identity); you can refer to this question

edit: to check the seed value; you can use:

IDENT_SEED ( 'table_or_view' )

see also: IDENT_INCR

edited May 23 '17 at 11:58

Community

1
1

answered Aug 22 '14 at 10:02

Kevin D

465
5
15

ty that answer my second question how i check if it increment or not – Asaf Shazar Aug 22 '14 at 10:07
I edited my answer to include information on how to check the seed and increment for your table. – Kevin D Aug 22 '14 at 10:11
Not work for me i try that sql1 = string.Format("SELECT IDENT_SEED('{0}.{1}') AS Identity_Seed", "tablename", "ColumnName"); str = doQuaeryWithReturn(FileName, sql1); MessageBox.Show(str[0]);// return me null – Asaf Shazar Aug 22 '14 at 10:19
You don't have to specify your column name; only your table name: sql1 = string.Format("SELECT IDENT_SEED('{0}') AS Identity_Seed", "tablename"); str = doQuaeryWithReturn(FileName, sql1); – Kevin D Aug 22 '14 at 10:20

Kami · Answer 2 · 2014-08-22T10:15:37.453

1

To get the result you require, try,

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

edited Aug 22 '14 at 10:15

answered Aug 22 '14 at 10:00

Kami

19,134
4
51
63

i dont really know if it 2 the 2 is for example i just wanna do quary that get the column's (that is auto_increment ) status the "Seed" and the "increment" – Asaf Shazar Aug 22 '14 at 10:07
@AsafShazar I have updated the answer to get all the fields you need – Kami Aug 22 '14 at 10:16

score 0 · Answer 3 · answered Aug 22 '14 at 10:16

You can check whether a column is an IDENTITY column with this statement:

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

To find out the seed value, the increment, and the last value used, use this statement:

SELECT 
    ColumnName = name,
    TableName = OBJECT_NAME(object_id),
    seed_value ,
    increment_value ,
    last_value 
FROM 
    sys.identity_columns

SQL Server : how I get AUTO_INCREMENT status

3 Answers3