Listing primary keys of a database in MySQL-MariaDB

Posted by in MariaDB, MySQL

A few days ago, I needed to ask to all tables of a database about what is the name of the primary key of each table.

The solution is easy, just using the data from the database information_schema, and by retrieving the data for the selected schema, this way:

SELECT TABLE_NAME as table, COLUMN_NAME 
FROM `information_schema`.`COLUMNS` 
WHERE (`TABLE_SCHEMA` = 'database')
 AND (`COLUMN_KEY` = 'PRI'); 

And …  for what do I need this query? Well, the origen of the problem was this: I created a view with the previuos SQL sentence and I call it campos_clave. Now, imagine you have a “Logs” table where you store this data: tabla, id, timestamp, iduser.

If you need to know more about the information from the Logs table, lets’ create a new view where you can get a register with a SQL sentence like this: SELECT * FROM table WHERE campoclave = id. Of course, we need yo use CONCAT as well!

With the Logs table, I just can have the table and the id affected, not enought data to write the query, beause I need to know what’s the name of the primary key. So, that’s where the initial SQL query comes to play:campos_clave:

SELECT lg.tabla, lg.id, lg.actualizado, 
CONCAT('SELECT * FROM ',lg.tabla,' WHERE ',pk.COLUMN_NAME,' = ',lg.id,';') as 'sentencia_sql'
FROM logs lg
INNER JOIN claves_clave pk USING (tabla)
ORDER BY lg.actualizado DESC

Maybe you won’t see any utility in having the a SQL query as a result, but in my case, it’s very useful because I can process it on a web project!

Of course, creating SQL queries as a results can have sense for actions like DELETE, INSERT or UPDATE instead of SELECTs. Of course, you can process the results with common_schema.eval (I talked on a previous post about this).

Have a nice day, and I hope it can be useful to you!