October 14, 2008 at 8:10 am
Hello,
I am new at this so please excuse the dumb question. Is there a way to count the number of columns (field names) in all the 'User' tables of a db without having to open each table and physically counting them? I did find a bit of sql code that I can use with query analyzer that will count the columns but I have to open each table and run it separately.The database I am working on has over 200 tables and a bazillion columns. I really don't want to have to open every table and run a query. Perhaps there is a 'stats' table hidden somewhere. I am using SQL Server Version 8 (2000).
Thanks!!!
M
October 14, 2008 at 9:23 am
I'm not even going to ask why you need to count the columns. 😛
This should give you a count of user table columns in the current database:
select count(c.name) from syscolumns c join sysobjects o
on c.id = o.id and o.xtype = 'U'
FYI, there are SQL 2000 forums on this site. Just scroll down a little further on the Forums page.
Greg
October 14, 2008 at 9:42 am
Thanks Greg! Worked like a charm. The reason I am counting the columns is we changed db vendors. The new db has 26 tables and 440 columns whereas the old db had 151 tables and 2666 columns. I am using the counts to justify that we didn't receive the entire db from the new vendor. That and the fact that my boss wants to make sure I am busy.
M
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply