Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Row count and space used for all tables in database Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 10:14 AM
Points: 27, Visits: 30
A very crisp and clean way to retrieve all the tables along with its row count

Row count of all tables in a database
Post #1027473
Posted Wednesday, May 25, 2011 12:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
Couple of items to note all of which assume a SQL 2005 platform as you say this code is for:

1) The call to DBCC UPDATEUSAGE in your ‘proc_records_per_database’ SP is unnecessary per BOL which states “Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.” So you may need to run it the first time you access DB’s upgraded from 2000 to 2005 but after that initial run this is just excess overhead.

2) Per BOL regarding sysindexes “This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead.” The older SQL 2000 sysindexes is replaced with sys.indexes . Unfortunately the newer sys.indexes does not include rowcount however you can get it thru the SQL 2005 view sys.partitions but if you link it to sys.indexes you’ll need to eliminate the duplication that you get joining the 3 items (sys.tables, sys.indexes & sys.partitions)

3) Even though you managed to avoid the literal use of a cursor you have 4 instances of RBAR (row by agonizing row) where you’re looping through some set/results.

4) It may be just me but it seems like there’s a lot going on here just to get the table name, rowcounts and space used. It does do it for all databases if one wishes however if one’s only looking for this info on a single DB which I think would be the more common scenario you can do this thru the join of sys.tables + sis.indexes + dbo.spt_values (located in the MASTER DB).


This is just my opinion.


Kindest Regards,

Just say No to Facebook!
Post #1114963
Posted Wednesday, May 25, 2011 12:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
sandeep.cs3 (11/29/2010)
A very crisp and clean way to retrieve all the tables along with its row count

Row count of all tables in a database


I looked at your rowcount sample and its short and sweet which is all the better in T-SQL in a SQL 2000 only environment.

If though you are working with SQL 2005 or higher, know that per BOL the use of older SQL 2000 table ‘sysindexes’ ( which in 2005 is a view included for backward computability) is not recommended as it will be going away and has been replaced with view sys.indexes . Unfortunately the newer sys.indexes does not include rowcount or an equivalent and so you must join another view (sys.partitions works) or some other object to get your equivalent rowcount value. Note too that depending on whether you have portioned tables or not the method you employ may or may not return the correct rowcount in a partitioned table.


Kindest Regards,

Just say No to Facebook!
Post #1114966
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse