SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row count and space used for all tables in database


Row count and space used for all tables in database

Author
Message
sandeep.cs3
sandeep.cs3
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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
YSLGuru
YSLGuru
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4236 Visits: 1667
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!
YSLGuru
YSLGuru
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4236 Visits: 1667
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search