SQL - Check if a column auto increments

27
October 25, 2019, at 2:10 PM

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?

Answer 1

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'
Answer 2

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.

Answer 3

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

Answer 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
READ ALSO
Create a list of actor pairs along with the number of films that they have in common

Create a list of actor pairs along with the number of films that they have in common

have this query so far for the above questionIm a student who is just starting so any help would be appreciated

50
Restored MySQL for Azure instance is exactly the same as original

Restored MySQL for Azure instance is exactly the same as original

I tried restoring my MySQL database to a week ago, but when I log into the newly created database it still has records from todayI tried several times and got the same result

37
Inserting Image to the MySQL database via Spring Boot

Inserting Image to the MySQL database via Spring Boot

I want to store image as a blob in MySQL database using Spring BootI have created the following model to perform crud operations on the database

14
In MYSQL can I disable for anyone and everyone the drop table (or a DB) query?

In MYSQL can I disable for anyone and everyone the drop table (or a DB) query?

I would like to totally disable anyone from dropping a table or DB from MYSQL using a query / injectionI have just started to read about users and permissions, as far as Im aware I have never created any users

28