CODEWITHSUNDEEP
mysqlsql
First Last
First Last

Reputation: 79

Counting the number of columns in the database

I've been trying to count the number of columns in a table in my database using the below statement:

    rs = st.executeQuery("SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '"+TableName+"'");
            while (rs.next()) {
               i = rs.getInt(1);
    }

For the below table table this code returns "6". If it only counts the columns it must return 3 and if it count columns of all record the returned value must be 12. My question is how come I get the 6 value.

|Username|   |Password|   |Level|
|Admin   |   |Admin   |   |1    |
|User    |   |User    |   |2    |
|Staff   |   |Staff   |   |3    |
|User1   |   |User1   |   |2    |

And how can I properly count the columns of a table ?

Upvotes: 0

Views: 119

Answers (3)

Kinaan Khan Sherwani
Kinaan Khan Sherwani

Reputation: 1534

I hope you are working in java. You can also try this

rs = st.executeQuery("select * from "+TableName+" limit 1");
ResultSetMetaData metadata = rs.getMetaData();
int columnsNumber = metadata.getColumnCount();

Upvotes: 0

Mihai
Mihai

Reputation: 26784

Also specify the db,you might have a table with the same name in other db

...FROM information_schema.columns WHERE table_schema = 'myDB'...

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

The following query returns the number of columns that you have. If you want to get 12 you would have to multiply by the number of rows in the table:

SELECT COUNT(*)
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='Yourdatabase' 
    AND `TABLE_NAME`='yourtable';

See it in sqlfiddle

Upvotes: 3

Related Questions