Menu
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?
575
Check if the table has any primary keys, and get them from the table_info table, using the query below: PRAGMA table_info("<table name here>"); 2. You can go through the returned results using a CURSOR , and only select ones that has "pk" column value different from 0, which indicates that is a Primary Key .
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add.
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
