Counting columns in a table using sql

I just started the project of SQL path.
I don’t think I have learned how to count columns of a table.
Would you let me know how to do that?

You can use this query

pragma table_info(table_name);

To get information about each of the columns in one of your tables. Here’s what it looked like when I ran it for one of the tables I’m currently working on in another project.

cid name type notnull dflt_value pk
0 reviewid INTEGER 0 None 0
1 title TEXT 0 None 0
2 artist TEXT 0 None 0
3 url TEXT 0 None 0
4 score REAL 0 None 0
5 best_new_music INTEGER 0 None 0
6 author TEXT 0 None 0
7 author_type TEXT 0 None 0
8 pub_date TEXT 0 None 0
9 pub_weekday INTEGER 0 None 0
10 pub_day INTEGER 0 None 0
11 pub_month INTEGER 0 None 0
12 pub_year INTEGER 0 None 0

You can use that query and just count the columns

Either that or you could just use this query:

SELECT * FROM table_name

and count the number of columns.

Hope this helps!

I’m curious as to why you need this. It’s not covered by pure SQL because it’s not a common need.

I was confused by this too.

The first part of the projects asks us to create a summary of the database, showing;

  • Each Table Name
  • Number of Rows in Each Table
  • Number of Attributes in Each Table (which I also took to mean number of columns)

It took me quite a while to work this out (or really find the answer after using Google!) and I have to say I was quite disappointed to see the table name + number of attributes calculated in the DQ solution are obtained simply by manually typing them in.

I think the question could have been a bit clearer, although to be honest I’m not sure it was even necessary as all the info can easily be obtained by browsing the data.

1 Like