Count of columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count of columns

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-08-14 : 11:19:44

Gurus
Is there anyway with which we can find out the count of columns in sql server?we can count rows in a table by count (*) but how can we count the columns in sql server
Plz help
regards
Nitin

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 11:23:30

have a look at syscolumns or information_schema.columns

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 11:23:31

I guess, if you need to, try something like:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMN WHERE TABLE_NAME in ('') --replace the '' with the list of table you want to count columns on.

this will only return the numner of column in a database, so you may need to merge this with a dynamic query to run this for all databases. (or use the undocumented sp_msforEachDB - which I don't recommend).

Why would you want this information? Are you trying to generate some kind of audit report?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-08-14 : 11:35:08

BINGO.....
Thanks
Yes we are trying to build one CSV

Thanks Everybody

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 12:20:05

If you do need to do this across databases, yhou may want to go with something similar to what I posted in the other thread you asked about count(*)'s.

i.e.


SET NOCOUNT ON
DECLARE 
	@SQLCommand varchar(8000),
	@Debug		int

SET @SQlCommand = ''
SET @Debug	= 1
SELECT @SQlCommand=@SQlCommand+
		'SELECT COUNT(*) as [Database_'+[Name]+'_#Columns] FROM ['+[Name]+'].INFORMATION_SCHEMA.COLUMNS;
' 
		FROM [master].sys.sysdatabases WHERE [Name] NOT IN ('Master','msdb','tempdb','model')

IF @Debug = 1 SELECT @SQlCommand
IF @Debug = 0 exec(@SQlCommand)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

chaudharypraveen
Starting Member

1 Post

Posted - 2010-10-04 : 10:06:30

I want to count the columns in table .what is the query for it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 07:11:18

quote:
Originally posted by chaudharypraveen

I want to count the columns in table .what is the query for it.

select count(*) from information_schema.columns
where table_name='your table'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources