Tuesday, 12 June 2012

MySQL : Find all Foreign Keys Constraints in a database (MySql)

I was really Unpleasant when I  failed to find out all foreign keys inside my database. Really, that was a great movement when I did it.My project manager was asking again and again , really I was so happy after the solution.

I can do it in MS SQL Server 2000/2008  , but in mysql its not quite easy.So, I share this in this blog, for help other developers to achive the goal.Below the query you can use it in mysql query editor :-



SELECT
f.table_schema as 'schema',
f.table_name as 'table' ,
f.column_name as 'column',
f.constraint_name as 'constraint_name'
FROM `information_schema`.`KEY_COLUMN_USAGE` f
where
f.table_schema='admin_kairali' and f.referenced_column_name  is not null


--Above for a single given table.



SELECT
f.table_schema as 'schema',
f.table_name as 'table' ,
f.column_name as 'column',
f.constraint_name as 'constraint_name'
FROM `information_schema`.`KEY_COLUMN_USAGE` f
where
 f.referenced_column_name  is not null

--For all table.