Page 1 of 1

Need Help: Query to find primary key of table

Posted: Fri Dec 19, 2008 6:10 am
by rajsekar2u
Hi friends

Is there any any to find column name to which primary key is set using mysql query...

Please Help me in this....

Thanks in advance

Re: Need Help: Query to find primary key of table

Posted: Fri Dec 19, 2008 6:37 am
by viraj
Hi

Try this one

Code: Select all

 
SELECT COLUMN_NAME FROM KEY_COLUMN_USAGE WHERE TABLE_NAME = 'name_of_your_table' AND TABLE_SCHEMA = 'name_of_your_database' AND CONSTRAINT_NAME = 'Primary'
 
Viraj

Re: Need Help: Query to find primary key of table

Posted: Fri Dec 19, 2008 6:45 am
by VladSun
[sql]SELECT       *FROM     `information_schema`.`columns`WHERE     `information_schema`.`columns`.`table_schema` = 'my_database_name'     AND `information_schema`.`columns`.`table_name` = 'my_table_name'     AND `information_schema`.`columns`.`column_key` = 'PRI'[/sql]

Or you can use the output of:
[sql]DESCRIBE MY_TABLE_NAME;[/sql]

EDIT: Nice one viraj. I like yours more than mine ;)