CODEWITHSUNDEEP
mysql
Morgan
Morgan

Reputation: 927

Show information about table columns and keys

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.

Upvotes: 2

Views: 156

Answers (2)

Paul Campbell
Paul Campbell

Reputation: 1976

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;

Upvotes: 1

Pankaj Gadge
Pankaj Gadge

Reputation: 2814

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';

Upvotes: 0

Related Questions