To get total number of columns in a table in sql

Question

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

Answer 1

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'

Answer 2

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.

Answer 3

In MS-SQL Server 7+:

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

Answer 4

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'

Answer 5

It can be done using:-

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

Answer 6

You can try below query:

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