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!