How to Count the Number of Columns in a Table Using SQL

To get total number of columns in a table in sql

SELECT COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG = 'database' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table'     

How do I count columns of a table

SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'tbl_ifo'

Count number of columns in SQL Server

one way

select count(*) from sys.columns 

another

select count(*) from information_schema.columns

The bottom one does not have the system tables

by table

select count(*),table_name from information_schema.COLUMNS
GROUP BY table_name

tables only

select count(*),c.table_name 
from information_schema.COLUMNS c
JOIN information_schema.tables t ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_Schema = t.TABLE_Schema
WHERE TABLE_TYPE = 'base table'  
GROUP BY c.table_name

views only

select count(*),c.table_name 
from information_schema.COLUMNS c
JOIN information_schema.tables t ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_Schema = t.TABLE_Schema
WHERE TABLE_TYPE = 'view'  
GROUP BY c.table_name

Count of Columns in temp table in SQL Server

SELECT COUNT(*)
FROM tempdb.sys.columns
WHERE object_id = object_id('tempdb..#mytemptable')

Count how many columns have a specific value

Try this:

SELECT ID, z.cnt
FROM mytable
CROSS APPLY (SELECT COUNT(*) AS cnt 
             FROM (VALUES (x1), (x2), (x3), (x4)) x(y)
             WHERE x.y > 0) z

This query makes use of a Table Value Constructor to create an in-line table whose rows are the columns of the initial table. Performing a COUNT on this in-line table, you can get the number of columns greater than zero.

I think this scales well if you have more than 4 columns.

Demo here

How to insert a count column into a sql query

With your edit, I see that you want a row ID (normally called row number rather than "count") which is best gathered from a unique ID in the database (person_id or some other unique field). If that isn't possible, you can make one for this report with ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, in your select statement.

select Name, ROW_NUMBER() OVER (ORDER BY Name DESC) AS ID,
Age, Gender
from customer

This function adds a field to the output called ID (see my tips at the bottom to describe aliases). Since this isn't in the database, it needs a method to determine how it will increment. After the over keyword it orders by Name in descending order.

---

Information on Counting follows (won't be unique by row):

If each customer has multiple entries but the selected fields are the same for that user and you are counting that user's records (summed in one result record for the user) then you would write:

select Name, count(*), Age, Gender
from customer
group by name, age, gender

This will count (see MSDN) all the user's records as grouped by the name, age and gender (if they match, it's a single record).

However, if you are counting all records so that your whole report has the grand total on every line, then you want:

select Name, (select count(*) from customer) as "count", Age, Gender
from customer

TIP: If you're using something like SSMS to write a query, dragging in columns will put brackets around the columns. This is only necessary if you have spaces in column names, but a DBA will tend to avoid that like the plague. Also, if you need a column header to be something specific, you can use the as keyword like in my first example.

W3Schools has a good tutorial on count()

The COUNT(column_name) function returns
the number of values (NULL values will not be counted) of the
specified column:

SELECT COUNT(column_name) FROM table_name;

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

The COUNT(DISTINCT column_name) function returns the number of
distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name; 

COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but
not with Microsoft Access.

SQL: Count number of columns in all tables, excluding views

This assumes SQL 2005 or higher

SELECT 
    t.name,
    count(c.name)
FROM 

    sys.tables t 
    inner join sys.columns c
    ON t.object_id = c.object_id

group by t.name

---

Related Topics