CODEWITHSUNDEEP
mysqlsql
ChathurawinD
ChathurawinD

Reputation: 784

How do you get whether a column is a primary key of a table from with all columns?

I am getting all the columns of all tables listed down of my data base with following query in mySQL 

SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position

I want to get all columns whether it's a primary key or not? For primary key columns to appear as y and non primary key columns to appear as n in this query. How can I achieve that with this query?

Upvotes: 2

Views: 123

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You can use COLUMN_KEY column and CASE WHEN:

SELECT column_name, CASE WHEN COLUMN_KEY = 'PRI' THEN 'y' ELSE 'n' END AS result
FROM information_schema.columns
WHERE table_schema = DATABASE()
   AND `table_name` = 'tab'     -- table name
ORDER BY `table_name`, ordinal_position

SqlFiddleDemo

Upvotes: 2

Related Questions