MS SQL Server - Number of columns for all tables in a database

Author

Number of columns for all tables in a database

JoelBarish

2006-10-24, 6:33 pm

Hi - I apologize in advance for the newbie question...

I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.

I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)

Is there a query that can output a grid listing the number of fields
per each table in a selected database?

If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.

I REALLY appreciate any help sent my way.

Cheers!
Joel

Kalen Delaney

2006-10-24, 6:33 pm

Hi Joel

No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.

There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:

SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME

To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.

exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'

If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)

Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)

Hopefully, this has given you a start...

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"JoelBarish" <JoelForBoards@gmail.com> wrote in message
news:1159539224.878544.293450@i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
>
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
>
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
>
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
>
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
>
> I REALLY appreciate any help sent my way.
>
> Cheers!
> Joel
>

Tibor Karaszi

2006-10-24, 6:33 pm

Just do a GROUP BY and you are there:

SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME

For number of rows, we need to know what version of SQL Server...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JoelBarish" <JoelForBoards@gmail.com> wrote in message
news:1159539224.878544.293450@i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
>
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
>
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
>
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
>
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
>
> I REALLY appreciate any help sent my way.
>
> Cheers!
> Joel
>

JoelBarish

2006-10-24, 6:33 pm

Thank you BOTH so much for your help.

I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.

We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...

Thanks!

Tibor Karaszi wrote:[color=darkred
]
> Just do a GROUP BY and you are there:
>
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
>
> For number of rows, we need to know what version of SQL Server...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "JoelBarish" <JoelForBoards@gmail.com> wrote in message
> news:1159539224.878544.293450@i42g2000cwa.googlegroups.com...

Tibor Karaszi

2006-10-24, 6:33 pm

Does the row count need to be exact? If not, use the sysindexes system table, something like:

SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt

And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname'
) WITH COUNT_ROWS

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JoelBarish" <JoelForBoards@gmail.com> wrote in message
news:1159544274.480475.86270@b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
>
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
>
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
>
> Thanks!
>
>
>
> Tibor Karaszi wrote:
>

wbpelen@yahoo.com

2006-10-24, 6:33 pm

Joel,

I think I am a little more bored than the rest of them.

--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb

IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END

CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200
) NOT NULL
, col_cnt int NULL )

DECLARE @db_name varchar(200
)
DECLARE @count as int
DECLARE @cmd varchar(2000)

SET @cmd = ''
SET @db_name = ''

DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null

OPEN dbs
FETCH NEXT FROM dbs into @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @db_name
SET @cmd = 'USE [' + @db_name + ']'
PRINT @cmd
EXECUTE ( @cmd)
SET @cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @db_name + ''', a.name, count(*)
FROM [' + @db_name + ']..sysobjects a, [' + @db_name + ']..syscolumns
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'

EXECUTE (@cmd)

PRINT @cmd

FETCH NEXT FROM dbs into @db_name

END
CLOSE dbs
DEALLOCATE dbs

-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs

Sponsored Links