Number of columns for all tables in a database

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
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.googlegr oups.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
>
|||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.googlegr oups.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
>
|||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:[vbcol=seagreen]
> 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.googlegr oups.com...
|||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.googlegro ups.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:
>
|||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_namevarchar(200) NOT NULL
, col_cntint NULL )
DECLARE @.db_namevarchar(200)
DECLARE @.count as int
DECLARE @.cmdvarchar(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
WHEREa.id= b.id
ANDa.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