3

Is it possible to retrieve the count of the number of columns a query returns? This can be easily done with a bound scripting language such as php, but I'm looking for db only solution.

Example:

CountCols(SELECT 'a','b','c')
    => 3
CountCols(SELECT * FROM information_schema.session_variables)
    => 2
JoshDM
  • 4,939
  • 7
  • 43
  • 72
Mikulas Dite
  • 7,790
  • 9
  • 59
  • 99
  • 2
    Out of curiosity -- what is this useful for? I can't think of a situation when I've needed to know how many columns a query returns, except in a programming context where I was doing something fairly generic/abstract. – ruakh Feb 14 '13 at 22:33

3 Answers3

3

Would this work for you?

select 
    count(*)
from
    `information_schema`.`columns`
where
    `table_schema` = 'my_table_schema' and `table_name` = 'my_table_name';

You only need to use table_schema if the table name exists in more than one database.

Based on your response comment, you are looking to count a dynamic number of columns. You may be able to do this with a temporary table, but you cannot access the data of a temporary table without possibly installing a patch.

Of note, there is a similar outstanding SO question asking how to select columns from a temporary table.

Community
  • 1
  • 1
JoshDM
  • 4,939
  • 7
  • 43
  • 72
  • That would be the idea, but I'm more looking for something generic. The problem is that I need to know number of columns of arbitrary results (as in example). Those are not stored in the info schema. – Mikulas Dite Feb 14 '13 at 22:39
  • Select them into a view or temporary table, and then access the information_schema? We can search for some patches that do this. – JoshDM Feb 14 '13 at 22:41
  • Here is a person who has a patch to add access to temporary tables: http://venublog.com/2010/02/03/show-temporary-tables/ ; Meanwhile, selecting columns from a temporary table is similar to this unanswered SO question: http://stackoverflow.com/questions/10443672/list-of-temporary-table-columns-mysql – JoshDM Feb 14 '13 at 22:47
  • Well, that seems somewhat dirty. I will definitely test it though. Thank you – Mikulas Dite Feb 14 '13 at 22:52
  • What did you ultimately end up doing? – JoshDM Mar 02 '13 at 18:41
  • I reimagined the original issue and managed to fit it to info schema, eliminating temporary tables. Thanks :) – Mikulas Dite Mar 02 '13 at 20:03
2

Well if you want to know the columns in a table just do:

DESCRIBE `table_name`

Otherwise there is no "real" way to get the number of columns in a select query since other than selecting * you select certain columns --> so you will know how many columns you are selecting.

Naftali
  • 144,921
  • 39
  • 244
  • 303
  • Though that is true, I can't programatically get the number of rows from `Describe` either. You've got a point I should generally know the columns though. It's just not always the case. – Mikulas Dite Feb 14 '13 at 22:38
  • Prety good. It shows a line counter on the left side of the result. It works for me. – Mateus Arruda Apr 07 '20 at 23:43
1

You'll find your answer here most likely: http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

Write a query off of that that takes a table name param and then query for columns of that table and sum that up.

RandomUs1r
  • 4,010
  • 1
  • 24
  • 44