CODEWITHSUNDEEP
sql-server-2008metadata
edgarmtze
edgarmtze

Reputation: 25048

Count number of columns in SQL Server

Is there a way to know the number of columns in SQL, something like count()...?

Upvotes: 3

Views: 25625

Answers (5)

Dustin Hodges
Dustin Hodges

Reputation: 4195

Select Count(*) From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='YourTableName'

or if you need to specify the schema

    Select Count(*) From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='YourTableName' and TABLE_SCHEMA = 'YourSchema'

Upvotes: 9

Abdelrahman Maharek
Abdelrahman Maharek

Reputation: 862

SELECT count(*)
FROM Database_Name.INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table_Name'

to Run 2 steps are needed : 1- choose the database_Name to your database name 2- Change Your Table Name Notes: if you didn't write database_Name like 

from INFORMATION_SCHEMA.COLUMNS

retrieves System database only

Upvotes: 0

user8128167
user8128167

Reputation: 7676

For a particular table:

select * from sys.tables
where name = 'mytable';

Get the object_id from this, then use:

select count(*) from sys.columns
where object_id = 831342026;

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134961

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

Upvotes: 18

datagod
datagod

Reputation: 1051

This should work across multiple RDBMS's:

select count(*) from INFORMATION_SCHEMA.COLUMNS

And if you want to get fancy:

select TABLE_NAME,
       count(*)
  from INFORMATION_SCHEMA.COLUMNS
group by TABLE_NAME
order by TABLE_NAME

Upvotes: 2

Related Questions