Querying InnoDB Tables

http://blog.mclaughlinsoftware.com/2014/12/27/querying-innodb-tables/

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database: ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation It’s easy to fix the error, except you must grant the process privilege. It’s a global privilege ans should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command: GRANT PROCESS ON *.* TO student; Then, you can run this query to resolve foreign keys to their referenced primary key column values: SELECT SUBSTRING_INDEX(f.id,'/',-1) AS constraint_name , CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) AS foreign_key_column , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)) AS foreign_key_column FROM innodb_sys_foreign f INNER JOIN innodb_sys_foreign_cols fc ON f.id = fc.id WHERE SUBSTRING_INDEX(f.for_name,'/',-1) = 'system_user_lab'; It returns the following: +---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | foreign_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) However, you can get the same information without granting the global PROCESS privilege. You simply use the table_constraints and key_column_usage tables, like this: SELECT tc.constraint_name , CONCAT(kcu.table_name,'.',kcu.column_name) AS foreign_key_column , CONCAT(kcu.referenced_table_name,'.',kcu.referenced_column_name) AS primary_key_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' AND tc.table_name = 'system_user_lab' ORDER BY tc.table_name , kcu.column_name; It prints the same output: +---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | foreign_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) Hope this helps.

from Planet MySQL http://ift.tt/1du18ol