February 25, 2004 at 6:20 am
I was recently tasked with verifying the results of a DTS copy objects package that cloned our production environment down to our Development and Test environments. The manager wanted to know the count of all the records on all all three servers.
After some brute force programming, I was able to capture the information.
Later I wrote a small stored procedure to do the same task. The stored proc takes one parameter and then calculates to total record count of all tables owned by dbo in the database.
But then it occured to me that perhaps Microsoft had already provided this functionality somewhere in the default install. I looked in the system stored procedures in master as well as the extended stored procedures, but I didn't find anything.
Has anyone seen a way to do this?
February 25, 2004 at 7:48 am
Don't think this is included. You could the (undocumented) SP_msForEachTable stored procedure to automatically loop through all the tables, getting the count and storing it in a temp table.
After that, you can get the total easily from the temp table.
February 25, 2004 at 11:18 pm
http://db.ittoolbox.com/documents/document.asp?i=2551
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308126
Select Sum(RowCnt) From SysIndexes
Where ObjectProperty(id,'isUserTable')=1 and indid in (0,1)
February 26, 2004 at 12:48 am
Try this
use northwind
go
sp_msforeachtable 'select ''?'', count(*) from ?'
February 26, 2004 at 2:32 am
February 26, 2004 at 2:49 am
use northwind
go
sp_msforeachtable 'sp_spaceused ''?'''
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply