0

I want to see the referenced table of previously created table.

What I Have :

I have a table zone_inout_log in DB trackcar. It contains various fields. I want to see the references of all the foreign keys used in this table.I don't know the referenced tables. All I have is table zone_inout_login which foreign keys are used , columns those are the foreign keys user_id , device_id , area_id etc.

What I Want

I want to see all the referenced tables like which table do I get user_id from anddevice_id,area_id` and so on.

What I Tried : 

 show table status from trackcar;

Also tried

show create table zone_inout_log;


| zone_inout_log | CREATE TABLE `zone_inout_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `device_id` int(11) NOT NULL,
  `area_id` int(11) NOT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  `inout_status` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
  KEY `device_id` (`device_id`),
  KEY `area_id` (`area_id`),
  KEY `lat` (`lat`),
  KEY `lng` (`lng`),
  KEY `date_time` (`date_time`),
  KEY `inout_status` (`inout_status`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

But didn't get referenced table name . If someone can suggest something ?

naggarwal11
  • 122
  • 1
  • 14
  • http://stackoverflow.com/questions/754512/mysql-how-do-i-find-out-which-tables-reference-a-specific-table – Abhik Chakraborty Oct 20 '15 at 10:01
  • @AbhikChakraborty : I've tried everything . I've tried this also. `select table_name from information_schema.KEY_COLUMN_USAGE where table_schema = 'my_database' and referenced_table_name = 'my_table_here' ` . It results in `empty set`. – naggarwal11 Oct 20 '15 at 10:15
  • Infact I've tried all the answers provide here also -> http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular – naggarwal11 Oct 20 '15 at 10:15
  • I want to ask can anyone hide this information of referenced tables to be viewed by others ? – naggarwal11 Oct 20 '15 at 10:16

1 Answers1

0

Please check it-

SELECT constraint_schema, 
table_name, 
column_name, constraint_name, 
referenced_table_schema, 
referenced_table_name, 
referenced_column_name  
FROM information_schema.KEY_COLUMN_USAGE 
WHERE referenced_table_schema ='your_db' 
AND referenced_table_name ='your_table';
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • This is also giving me `empty set` as its O/p. Is there any way to hide references ? May be the references has been made hidden ? – naggarwal11 Oct 20 '15 at 12:36
  • check if you are getting any row by following query - "SELECT constraint_schema, table_name, column_name, referenced_table_schema, referenced_table_name, referenced_column_name FROM information_schema.KEY_COLUMN_USAGE WHERE constraint_name IS NOT NULL AND constraint_name<>'PRIMARY' LIMIT 10;" – Zafar Malik Oct 20 '15 at 12:49