Table Columns count

Question

Table Columns count

Ed7

SSCrazy Eights

Points: 8516
More actions
April 16, 2009 at 8:02 pm

#208265

Hello Room,
I need to know how many columns in a table.
Can somone give me the t-sql statement?
Thanks,
Edwin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

You can use INFORMATION_SCHEMA.COLUMNS. You may need to add a schema filter also if you have the same table in multiple schemas.

select * from INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = 'Put Table Name Here'

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 2

I executed the following T-sripts;

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = ‘dbo.Table_A’;

Go

Result:

0

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 3

SELECT Count(*) FROM sys.COLUMNS

Where object_name([object_id]) = 'TABLENAME'; --without DBO

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 4

There is no sys.columns in SQL Server 2000.

I executed the following t-sql:

SELECT COUNT(*) from dbo.syscolumns

where object_name([object_id]) = 'Table_A' no dbo.

Invalid column name 'object_id'

Brad Mullins-359749 SSC Veteran Points: 214 More actions · Answer 5

Edwin (4/17/2009)
There is no sys.columns in SQL Server 2000.
I executed the following t-sql:
SELECT COUNT(*) from dbo.syscolumns
where object_name([object_id]) = 'Table_A' no dbo.
Invalid column name 'object_id'

dbo.syscolumns and sys.columns are two different things. If you use dbo.syscolumns you need to say:

SELECT COUNT(*) from dbo.syscolumns

where object_name(id) = 'Table_A'

If you use sys.columns you would say:

SELECT COUNT(*) from sys.columns

where object_name(object_id) = 'Table_A'

JacekO SSCertifiable Points: 6610 More actions · Answer 6

Edwin (4/17/2009)
There is no sys.columns in SQL Server 2000.

This is 2005 forum

For 2000 you could use this.

SELECT SO.name, COUNT(*)

FROM syscolumns SC

INNER JOIN sysobjects SO ON SC.id = SO.id

WHERE SO.type = 'U'

GROUP BY SO.name

ORDER BY SO.name

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 7

Jayco,

Thanks for your t-sql. It works.

Bravo solutions 🙂

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 8

Edwin (4/17/2009)
I executed the following T-sripts;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = ‘dbo.Table_A’;
Go
Result:
0

I just wanted to clarify that you need to specify the table name and the schema name in two seperate columns for this to work. For example...

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = 'Table_A' and TABLE_SCHEMA = 'dbo'