How can I count the number of columns in a MySQL table using C#?

Question

I can select the number of rows returned by a query when I select everything by doing the following:

SELECT Count(*) FROM `foo`.`bar`;

However, I now want to select how many columns are returned. Is there an easy way to do this?

There's one. The "column" that contains the count of all the rows in the table. — Glen Solsberry
– Glen Solsberry, Commented Mar 13, 2012 at 16:48
Do you really need this inside mysql or just inside your application? if the latter, which language is it written in? — Sirko
– Sirko, Commented Mar 13, 2012 at 16:49
Inside my application. I am trying to write an function that takes a MySql query as a parameter and returns a 2d array and obviously the amount of columns and rows differs each time, in C#! — JMK
– JMK, Commented Mar 13, 2012 at 16:50

Michael Berkowski · Accepted Answer · 2012-03-13 16:49:13Z

3

Use the information_schema database.

SELECT 
  COUNT(COLUMN_NAME) AS numcols
FROM COLUMNS
WHERE 
  TABLE_NAME = 'bar'
  AND TABLE_SCHEMA = 'foo'

To put the two together, use:

SELECT
  (SELECT COUNT(*) FROM foo.bar) AS numrows,
  (SELECT 
      COUNT(COLUMN_NAME) AS numcols
    FROM COLUMNS
    WHERE 
      TABLE_NAME = 'bar'
      AND TABLE_SCHEMA = 'foo'
  ) AS numcols

answered Mar 13, 2012 at 16:49

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Burhan Khalid · Accepted Answer · 2012-03-13 17:01:20Z

2

DESCRIBE someTable and then mysql_count_rows() on the result.

Since the question has to do with C#, I think this should work - but I haven't done C# in a long time.

MySqlConnection connection = new MySqlConnection(#...);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "DESCRIBE sometable";
connection.Open();
Reader = command.ExecuteReader();
Reader.FieldCount # this has the count of rows, and hence columns

edited Mar 13, 2012 at 17:01

answered Mar 13, 2012 at 16:50

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

2 Comments

CanSpice Over a year ago

The questioner is using C#, so PHP's mysql_count_rows() won't help him here. You answered before he clarified this in a comment though, I think...

JMK Over a year ago

Yeah @burhan answered before I clarified this! Apologies.

RolandoMySQLDBA · Accepted Answer · 2012-03-13 17:07:13Z

1

If your table mydb.mytable is MyISAM, the following should work well:

SELECT
    row_count,column_count
FROM
    (
        SELECT table_rows row_count
        FROM information_schema.tables
        WHERE table_schema = 'mydb'
        AND table_name = 'mytable'
    ) rowcount,
    (
        SELECT MAX(ordinal_position) column_count
        FROM information_schema.columns
        WHERE table_schema = 'mydb'
        AND table_name = 'mytable'
    ) columncount
;

answered Mar 13, 2012 at 17:07

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

1 Comment

JMK Over a year ago

Unfortunately it's InnoDB, but I didn't specify this previously.

Collectives™ on Stack Overflow

How can I count the number of columns in a MySQL table using C#?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related