CODEWITHSUNDEEP
c#sqlsql-server
Asaf Shazar
Asaf Shazar

Reputation: 1065

SQL Server : how I get AUTO_INCREMENT status

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

Upvotes: 1

Views: 2312

Answers (3)

marc_s
marc_s

Reputation: 754518

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

Upvotes: 0

Kami
Kami

Reputation: 19407

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

Upvotes: 2

Kevin D
Kevin D

Reputation: 485

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_SEED

to check the increment, you can use:

IDENT_INCR ( 'table_or_view' )

see also: IDENT_INCR

Upvotes: 2

Related Questions