/* Show all key/indexed columns and their referenced table/column (if any) */ 

SELECT  table_name, column_name, constraint_name, ordinal_position,    
        referenced_table_name, referenced_column_name      
FROM    information_schema.KEY_COLUMN_USAGE     
WHERE   table_schema = 'demo';                              


/* Show all key/indexed columns and their referenced table/column (if any)     */ 
/* Joining the above to table_constraints for a more complete key/index query  */ 

SELECT  k.table_name, k.column_name, k.constraint_name, t.constraint_type, 
	k.ordinal_position, k.referenced_table_name, k.referenced_column_name
  FROM  information_schema.KEY_COLUMN_USAGE   k
  JOIN  information_schema.TABLE_CONSTRAINTS  t
    ON  k.table_name      = t.table_name
   AND  k.constraint_name = t.constraint_name 
 WHERE  k.table_schema = 'demo';


/* Show all key/indexed columns and their referenced table/column (if any) */ 
/* Including indexes unique and not unique. (This is view v_key_column)    */ 

SELECT table_name, column_name, 'FOREIGN KEY' AS constraint_type,  
       constraint_name, ordinal_position,
       referenced_table_name as ref_table_name, referenced_column_name as ref_column_name    
    FROM information_schema.key_column_usage  
   WHERE referenced_table_name is not null
UNION
SELECT table_name, column_name, 
       CASE WHEN index_name = 'PRIMARY' THEN ' PRIMARY KEY'
            WHEN non_unique =  1        THEN 'INDEX'
            WHEN non_unique =  0        THEN 'UNIQUE INDEX'
       END,
       index_name, seq_in_index, null, null
    FROM information_schema.statistics
 ORDER BY 1,3,4,5,2;