It's kind of strange, but it works. Unfortunately, you can't nest
sp_msforeachtable inside sp_msforeachdb because of cursor name clashing,
and you have to use CHAR(63) to avoid the sp_msforeachdb from picking up
the ? needed for use by sp_msforeachtable.

Run this in ISQL/W, then paste the output back into the query window and
run that.

-- meta-sql to count all tables in all dbs
DECLARE @s varchar(1000)
SET @s = '
DECLARE @s varchar(1000)
SET @s = ''SELECT ''''count of table '''' + DB_NAME() + ''''.'' +
CHAR(63) + '' = '''' + CONVERT(varchar(10), COUNT(*)) FROM '' +
CHAR(63)'
PRINT @s
EXEC sp_msforeachdb @command1 = 'SELECT ''EXEC ?.dbo.sp_msforeachtable
@command1 = @s'''

-----Original Message-----
From: Chip Smith [mailto:[Email Address Removed]
Sent: Friday, November 21, 2003 3:57 PM
To: LazyDBA.com Discussion
Subject: Rowcounts in sql server



I want to count all of the rows for all of the user tables in all of the
databases, one at a time. Can this be done from within sql server or do
I need seperate procs for each db. There are many db's.

TIA

-- chip
MS Sql Server LazyDBA home page