1

Wondering if there's a way to get MySQL to return the column names when the query result returns no rows? The issue is that our system has multiple large queries sometimes:

SELECT * FROM table

SELECT table1.*, table2.field1, table2.field2

SELECT table1.field1 AS f1, SUM(table2.field1) AS f2

etc. So only way to get the column names when the returned result is empty, would be to parse the queries, and attempt to run a query on the information_schema table. Which is possible, but would be rather complex. Any ideas?

raina77ow
  • 103,633
  • 15
  • 192
  • 229
r0tterz
  • 198
  • 1
  • 2
  • 10
  • 2
    How do you expect to handle differences in data types? e.g. can you anticipate receiving 'myDateColumn' instead of a date object? – Cᴏʀʏ Sep 09 '14 at 22:24
  • @raina77ow: referred [MySQL query to get column names?](http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names) isn`t answer to this question, as this question is about how to get column names from **query**, not **table** – Rimas Sep 11 '14 at 10:47
  • @Rimas Fair point, voting for reopen. – raina77ow Sep 11 '14 at 11:31

3 Answers3

3

Some PHP interfaces for MySQL have a function for result set metadata, which should return information even for a result set with zero rows.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

One technique that you can use is to create a view on the query and then query information_schema with the view name.

That is, something like this:

create view v_JustForColumns as
    <your query here>;

select *
from information_schema.columns
where table_name = 'v_JustForColumns';

drop view v_JustForColumns;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try the below one. Here replace the YOUR_SCHEMA with your actual schema name and YOUR_TABLENAME with your actual table name:

select column_name from information_schema.columns  
where not exists(select * from YOUR_SCHEMA.YOUR_TABLENAME) 
and  table_name='YOUR_TABLENAME';
zakaria
  • 426
  • 2
  • 15