CODEWITHSUNDEEP
sql-server
user42348
user42348

Reputation: 4319

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?

Upvotes: 74

Views: 226163

Answers (13)

Sanoop Nair
Sanoop Nair

Reputation: 1

Another way with sys.columns

SELECT  COUNT(c.name) 'Total Columns' FROM sys.columns c WHERE c.object_id = OBJECT_ID( 'Table_Name' )

Upvotes: 0

Talha Tayyab
Talha Tayyab

Reputation: 27375

select count(1) from information_schema.columns where table_name = 'yourtablename'

Upvotes: 0

Eddie Kumar
Eddie Kumar

Reputation: 1480

One of the quickest ways to count columns in specific table is to look at max_column_id_used column in sys.tables for that table:

USE your_db
SELECT  name, max_column_id_used [columns_count]
FROM    sys.tables
WHERE   name IN ('your_table')

This avoids any aggregations/COUNT() function too, providing better performance especially if you need to run it against many tables.

HTH

Upvotes: 2

SINEHAN S
SINEHAN S

Reputation: 11

To get the total number of columns in table.

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

Upvotes: 1

Nur Uddin
Nur Uddin

Reputation: 1840

It can be done using:- 

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

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340211

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

Upvotes: 119

Derreck Dean
Derreck Dean

Reputation: 3766

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.

Upvotes: 0

Rasool
Rasool

Reputation: 67

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

Upvotes: 6

Haider Ali
Haider Ali

Reputation: 13

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'

Upvotes: 0

Peymankh
Peymankh

Reputation: 2046

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'

Upvotes: 45

banti
banti

Reputation: 21

You can try below query:

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

Upvotes: 2

Manish
Manish

Reputation: 517

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'

Upvotes: 4

Graviton
Graviton

Reputation: 83254

In MS-SQL Server 7+:

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

Upvotes: 19

Related Questions