SQL - Check if a column auto increments
Solution 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'
Solution 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.
Solution 3
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
Solution 4
Run: describe 'table_name'; In column EXTRA is what you looking for
Aust
<3 JavaScript is my favorite language. 2 millennia ago, Jesus Christ founded His church - but with the persecution of the apostles and the saints, that church (and the pure doctrine of Christ) crumbled. Around 200 years ago, Jesus Christ restored His church (along with His pure doctrine) through the Prophet Joseph Smith, and He wants you to come and participate in the blessings. Learn more at comeuntochrist.org. Why am I here? | Where do I go when I die?
Updated on July 09, 2022Comments
-
Aust almost 2 years
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?