SQL - Check if a column auto increments

Question

I am trying to run a query to check if a column auto increments. I can check type, default value, if it's nullable or not, etc. but I can't figure out how to test if it auto increments. Here is how I am testing for those other things:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND COLUMN_NAME = 'my_column'
AND DATA_TYPE = 'int'
AND COLUMN_DEFAULT IS NULL
AND IS_NULLABLE = 'NO'
--AND AUTO_INCREMENTS = 'YES'

Unfortunately there is no AUTO_INCREMENTS column to compare against. So how can I test if a column auto increments?

Which DBMS are you using? PostgreSQL? SQL Server? MySQL? – Dec 07 '12 at 22:28 — , Dec 07 '12 at 22:28
@a_horse_with_no_name - SQL Server and MySQL. – Aust Dec 07 '12 at 22:29 — Aust, Dec 07 '12 at 22:29

score 63 · Accepted Answer · answered Dec 07 '12 at 22:31

63

For MySql, Check in the EXTRA column:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
    AND COLUMN_NAME = 'my_column'
    AND DATA_TYPE = 'int'
    AND COLUMN_DEFAULT IS NULL
    AND IS_NULLABLE = 'NO'
    AND EXTRA like '%auto_increment%'

For Sql Server, use sys.columns and the is_identity column:

SELECT 
    is_identity
FROM sys.columns
WHERE 
    object_id = object_id('my_table')
    AND name = 'my_column'

answered Dec 07 '12 at 22:31

Michael Fredrickson

36,839
5
92
109

3

Not correct for SQL Server, since you can have an identity column which does not auto increment when identity insert is set to ON – Chris Sim Sep 24 '18 at 10:26
@ChrisSim why its not correct, its working – vishal Oct 23 '22 at 11:26

score 11 · Answer 2 · edited May 23 '17 at 12:18

11

Assuming MySQL, the EXTRA column will indicate whether it is AUTO_INCREMENT.

| TABLE_CATALOG | TABLE_SCHEMA | ... |          EXTRA | ... |
-------------------------------------------------------------
|           def |   db_2_00314 | ... | auto_increment | ... |

And for MSSQL, see here.

edited May 23 '17 at 12:18

Community

1
1

answered Dec 07 '12 at 22:30

Kermit

33,827
13
85
121

score 4 · Answer 3 · edited Aug 19 '19 at 11:26

4

this works for sql server:

    Select COLUMN_NAME, TABLE_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = 'dbo'
    and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    order by TABLE_NAME

edited Aug 19 '19 at 11:26

AzmahQi

378
1
15

answered Aug 31 '13 at 00:49

Dalkiel

49
1

Can you expand on how this works to solve the requester's problem? – Ro Yo Mi Aug 31 '13 at 01:07
1

I know slightly dated question, but for anyone looking historically and wondering, I can confirm that this solution DOES WORK if you don't want to access the sys schema. – Raiden616 Mar 21 '14 at 11:38

score 3 · Answer 4 · answered Nov 22 '13 at 21:40

3

Run: describe 'table_name'; In column EXTRA is what you looking for

answered Nov 22 '13 at 21:40

Johny

39
1

Nice and concise. Strange that you can't SELECT the Extra column, though. – Apr 29 '22 at 22:27

score 0 · Answer 5 · answered Mar 21 '23 at 20:09

In Postgres, try this:

SELECT column_default
FROM information_schema.columns
WHERE
    table_name = 'my_table'
    AND column_name = 'my_column';

And look for something like this (as opposed to NULL): nextval('my_schema.my_table_my_column_seq'::regclass)

SQL - Check if a column auto increments

5 Answers5

Linked