[sql-server] To get total number of columns in a table in sql

I need a query in sql to get total columns in a table.Can anybody help?

In MS-SQL Server 7+:

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'

Correction to top query above, to allow to run from any database

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

In my situation, I was comparing table schema column count for 2 identical tables in 2 databases; one is the main database and the other is the archival database. I did this (SQL 2012+):

DECLARE @colCount1 INT;
DECLARE @colCount2 INT;

SELECT @colCount1 = COUNT(COLUMN_NAME) FROM MainDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable';
SELECT @colCount2 = COUNT(COLUMN_NAME) FROM ArchiveDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable';

IF (@colCount1 != @colCount2) THROW 5000, 'Number of columns in both tables are not equal. The archive schema may need to be updated.', 16;

The important thing to notice here is qualifying the database name before INFORMATION_SCHEMA (which is a schema, like dbo). This will allow the code to break, in case columns were added to the main database and not to the archival database, in which if the procedure were allowed to run, data loss would almost certainly occur.

You can try below query:

select 
  count(*) 
from 
  all_tab_columns
where 
  table_name = 'your_table'

Select Table_Name, Count(*) As ColumnCount
From Information_Schema.Columns
Group By Table_Name
Order By Table_Name

This code show a list of tables with a number of columns present in that table for a database.

If you want to know the number of column for a particular table in a database then simply use where clause e.g. where Table_Name='name_your_table'

The below query will display all the tables and corresponding column count in a database schema

SELECT Table_Name, count(*) as [No.of Columns]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'dbo' -- schema name
group by table_name

This query gets the columns name

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'YourTableName'

And this one gets the count

SELECT Count(*) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'YourTableName'

It can be done using:-

SELECT COUNT(COLUMN_NAME) 'NO OF COLUMN' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Address'