Show information about table columns and keys

Question

In order to retrieve information about columns, e.g: their name, ordinal position, datatype, etc, I use the following query

SELECT * 
FROM   information_schema.`COLUMNS` 
WHERE  TABLE_SCHEMA = 'myDbName' 
   AND TABLE_NAME = 'myTable'

This returns all the information I need except information about which columns are PK and FK, especially FKs. I noticed that one of the columns returned by that query is COLUMN_KEY. For PKs, this column suffices because it has a value of PRI, but for FKs, this column either has a MUL or nothing. Is there a way to retrieve information about columns that includes key information reliably?

I don't need to know details about the PK or FK, I just want to get information about the columns AND to know if they are PK or FK.

`MUL` basically means that this key can be repeated. If you have a `UNIQUE` column key, you'd see `UNI` — Phiter, Mar 28 '18 at 20:22
This might help https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column — Pankaj Gadge, Mar 28 '18 at 20:24
@Pankaj_Gadge how would I go about joining that with what I already have? — Morgan, Mar 28 '18 at 20:40
@Morgan Added the answer - let me know if that helps you in any way. — Pankaj Gadge, Mar 28 '18 at 20:56

score 1 · Accepted Answer · answered Mar 29 '18 at 12:46

This should give you an overview of columns, keys and FK constraints. I've arranged the keys to distinguish between those that reference another table and those that don't, which is somewhat against the natural order of the underlying views. You can add or remove columns to suit your requirement but you will get to a level of detail where you might as well just run show create table table_name.

N.B., this should work in mysql > 5.7.6 , if you are using an earlier version then you'll need to remove generation_expression from the SELECT.

SELECT c.`ordinal_position` AS '#', 
  c.`column_name` AS 'Name', 
  c.`column_type` AS 'Type', 
  c.`is_nullable` AS 'Allow NULL', 
  IFNULL(c.`column_default`,'') AS 'Default',
  CONCAT(c.`extra`, ' ',c.`generation_expression`) AS 'Extra',
  IFNULL((SELECT GROUP_CONCAT(CONCAT(IF(s.`non_unique` = 0 ,'*',''),s.`index_name`, '(', s.`seq_in_index`,')'))
    FROM `information_schema`.`statistics` s  
    WHERE s.`table_schema` = c.`table_schema` 
    AND s.`table_name` = c.`table_name` 
    AND s.`column_name` = c.`column_name` 
  ),'') as 'Key name(pos) *=unique',
  IFNULL((SELECT GROUP_CONCAT(
    CONCAT(k.`constraint_name`, ': ', k.`referenced_table_name`,' (', k.`referenced_column_name`,')')) 
    FROM `information_schema`.`key_column_usage` k  
    WHERE k.`table_schema` = c.`table_schema` 
    AND k.`table_name` = c.`table_name` 
    AND k.`column_name` = c.`column_name`
    AND k.`referenced_table_name` IS NOT NULL
  ),'') AS 'FK name: table(column)'
FROM `information_schema`.`columns` c
WHERE c.`table_schema` = 'dbname' 
AND c.`table_name` = 'table_name'
ORDER BY c.ordinal_position;

This is impressive work. That's exactly what I wanted. – Morgan Mar 30 '18 at 16:00 — Morgan, Mar 30 '18 at 16:00

score 0 · Answer 2 · answered Mar 28 '18 at 20:55

0

Assuming you're using INNODB for your tables, you can find out if a key is foreign key or primary key using the below script

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY')
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

answered Mar 28 '18 at 20:55

Pankaj Gadge

2,748
3
18
25

This works, but is there a way to include this query or join it with the one that shows stuff from ``information_schema.`COLUMNS`` without having to run two queries? – Morgan Mar 28 '18 at 21:00

Show information about table columns and keys

2 Answers2