Is there a way in Enterprice Manager or Query-analyzer (or ??) to get a Simple List of All the Tables in a Database and the number of rows in each Table? (icing would be to also see K-bytes consumed by each table). I'm running Solomon IV Financials, and its database has about 200 Tables. I want to monitor the ROW COUNTS in each table. The only way I've found to see Row Counts is to open Enterprixe Manager, and right-click Properties on each Row 'one-by-one'. There's got to be a way to "see them all"!! Thanks. cjgaddy@kpseafood.com
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to List #ROWS in Every Table in Database? 1
- Thread starter cjgaddy
- Start date
-
1
- #2
tlbroadbent
MIS
You can use sp_MSforeachtable stored procedure.
To print a simple list of table names and row counts.
exec sp_MSforeachtable 'print ''?'' select count(*) from ?'
OR
exec sp_MSforeachtable 'select count(*) "?" from ?'
To get more table information combine sp_MSforeachtable with sp_spaceused.
exec sp_MSforeachtable 'exec sp_spaceused ?'
sp_MSforeachtable is documented on several web sites including Terry
The reason why worry kills more people than work is that more people worry than work. - Robert Frost
To print a simple list of table names and row counts.
exec sp_MSforeachtable 'print ''?'' select count(*) from ?'
OR
exec sp_MSforeachtable 'select count(*) "?" from ?'
To get more table information combine sp_MSforeachtable with sp_spaceused.
exec sp_MSforeachtable 'exec sp_spaceused ?'
sp_MSforeachtable is documented on several web sites including Terry
The reason why worry kills more people than work is that more people worry than work. - Robert Frost
- Thread starter
- #3
you can also use exec sp_MStablespace N'[dbo].[tabname]' to get the rowcount....
the advantage of doing this would the that you are not using count(*) which would be overhead on a large table. disadvantage may be that the count that you get is slightly off.
this in combination with Terry's suggestion of MSforeachtable might be faster.
might depend on your requirements. Paul
the advantage of doing this would the that you are not using count(*) which would be overhead on a large table. disadvantage may be that the count that you get is slightly off.
this in combination with Terry's suggestion of MSforeachtable might be faster.
might depend on your requirements. Paul
- Thread starter
- #5
Can't get Terry's 'Space' suggestion to work (Query Analyzer):
exec sp_MSforeachtable 'exec sp_spaceused ?'
I get this error:
Server: Msg 170, Level 15, State 1, Line -1074292529
Line 1: Incorrect syntax near '.'.
Also, Dave, how do you weave your exec sp_MStablespace N'[dbo].[tabname]' into the MSforeachtable procedure? What's the Exact SQL statement I need to use.
Thanks much guys - amazing forum!
exec sp_MSforeachtable 'exec sp_spaceused ?'
I get this error:
Server: Msg 170, Level 15, State 1, Line -1074292529
Line 1: Incorrect syntax near '.'.
Also, Dave, how do you weave your exec sp_MStablespace N'[dbo].[tabname]' into the MSforeachtable procedure? What's the Exact SQL statement I need to use.
Thanks much guys - amazing forum!
- Thread starter
- #6
tlbroadbent
MIS
Sorry. The correct syntax is
exec sp_MSforeachtable "exec sp_spaceused '?'"
Terry
The reason why worry kills more people than work is that more people worry than work. - Robert Frost
exec sp_MSforeachtable "exec sp_spaceused '?'"
Terry
The reason why worry kills more people than work is that more people worry than work. - Robert Frost
just a thought, but if you want to get real cool, you could in a stored procedure:
open a cursor and select table names from sysobjects.
extract from the sp_MStablespace the code and copy out the sql they use to get rowcounts ect.... (it's fairly simple sql).
get all this info and insert it into a table in a stats database you create.
do this for every table you want stats for (all of them i preusme)
put it in a regular schedule.
I have done this for a variety of cap planning type numbers and it's fun and gives sometimes very interesting info. also easy to query and put in a spreadsheet....
just a thought Paul
open a cursor and select table names from sysobjects.
extract from the sp_MStablespace the code and copy out the sql they use to get rowcounts ect.... (it's fairly simple sql).
get all this info and insert it into a table in a stats database you create.
do this for every table you want stats for (all of them i preusme)
put it in a regular schedule.
I have done this for a variety of cap planning type numbers and it's fun and gives sometimes very interesting info. also easy to query and put in a spreadsheet....
just a thought Paul
- Thread starter
- #9
Terry, I really like this one:
exec sp_MSforeachtable 'select count(*) "?" from ?'
Output looks like this (snipped a bit):
name rows reserved data
APAdjust 63902 57736 KB 56656 KB ...
P_Balances 4446 4544 KB 4440 KB ...
etc
etc
Nice getting the #ROWS and SPACES USED side-by-side. Easy to paste into Excel too.
Thanks much! Jack
exec sp_MSforeachtable 'select count(*) "?" from ?'
Output looks like this (snipped a bit):
name rows reserved data
APAdjust 63902 57736 KB 56656 KB ...
P_Balances 4446 4544 KB 4440 KB ...
etc
etc
Nice getting the #ROWS and SPACES USED side-by-side. Easy to paste into Excel too.
Thanks much! Jack
- Status
Similar threads
- Locked
- Question
- Locked
- Question