There are huge tables in my mysql database, I want to get all the tables with auto increment columns and the columns' names. Could anyone teach me how to do that ?
Asked
Active
Viewed 930 times
1
-
1Possible duplicate of [How to see indexes for a database or table in MySQL?](https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table-in-mysql) – Raymond Nijland Oct 21 '19 at 11:15
-
@RaymondNijland sorry, I don't think so. I am considering to get the information from information_schema.Columns. – Angle Tom Oct 21 '19 at 11:19
1 Answers
1
I think you can get that information from the COLUMNS table in the INFORMATION_SCHEMA schema.
E.g.
select TABLE_NAME, COLUMN_NAME from COLUMNS where `COLUMN_KEY` = 'PRI' and EXTRA like '%auto_increment%'
brass monkey
- 5,841
- 10
- 36
- 61
-
No be aware you can define a PRIMARY KEY without defining a AUTO_INCREMENT.. -> https://www.db-fiddle.com/f/jbADd4roKNmxXca4C2i5QY/0 – Raymond Nijland Oct 21 '19 at 11:19
-
-
Well in that table you would not have an auto increment on column a. Try to do two inserts without defining the column a. – brass monkey Oct 21 '19 at 11:25
-
That was mean as a "example" case.. @LazerBass , [this](https://www.db-fiddle.com/f/jbADd4roKNmxXca4C2i5QY/0) would make more sense.. – Raymond Nijland Oct 21 '19 at 11:27
-
using `EXTRA LIKE '%auto_increment%'` instead might be a bit better as extra can have multiple values from how the manual explains the extra coiumn.. – Raymond Nijland Oct 21 '19 at 11:43
-