Row count and space used for all tables in database

Question

Post reply

Row count and space used for all tables in database

skra

SSCommitted

Points: 1614
More actions
March 8, 2008 at 5:30 pm

#183878

Comments posted to this topic are about the item Row count and space used for all tables in database

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

gtossa SSC Veteran Points: 200 More actions · Answer 1

Very powerful script. Do you have the SQL Server 2000 version of this script?

Thank you,

GT

skra SSCommitted Points: 1614 More actions · Answer 2

There has been added a version of this procedure for SQL Server 2000.

It is waiting for approval now.

M Chabot SSCrazy Points: 2511 More actions · Answer 3

Maybe add

order by cat_name,sch_name,tab_name

for the select statement from the temp table.

khilanvinchhi SSC Enthusiast Points: 125 More actions · Answer 4

Hi Rafal,

How about this code??

use [YourDatabase];

select

a1.object_id

,a2.name

, a1.used_page_count * 8 as total_used_pages

, cast(a1.used_page_count * 8 /1000 as varchar(10)) + 'MB' as SIZE_total_used_pages

, a1.reserved_page_count * 8 as total_reserved_pages

, a1.row_count

from sys.dm_db_partition_stats a1

inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )

left outer join sys.indexes a3 on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) )

where (select count(distinct partition_number)

from sys.dm_db_partition_stats a4

where (a4.object_id = a1.object_id)) >= 1 and a2.type <>'S'

--and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space

order by a1.used_page_count desc, a2.name asc, a1.index_id, a1.partition_number

I have not compared my code as against yours.. just thought of putting it up here.. so did I. 😀

Will be glad if you replied on its worth!!! 🙂

Thanks,

Khilan

A_A_R_T SSC Eights! Points: 900 More actions · Answer 5

Hi there,

Thanks for this script...I was looking for a script like this. I tried both the SQL2005 and the SQL2000 script. No problem with the first script but the second wouldn't run.

Msg 170, Level 15, State 1, Procedure proc_records_per_database, Line 43

Line 43: Incorrect syntax near '?'.

I found a little typo in the script when creating the temp table:

create table #temp_table_list

(

rec_id int identity(1, 1) not null,

cat_name sysname not null,

sch_name sysname not null,

tab_name sysname not null,

row_count bigint not null default 0,

reserved_pages bigint not null default 0,

reserved bigint not null default 0,

pages bigint not null default 0,

data bigint not null default 0,

used_pages bigint not null default 0,????

used bigint not null default 0,

index_size bigint not null default 0,

not_used bigint not null default 0,

primary key(rec_id)

)

There are some unrecognized spaces (perhaps a tab) after "used_pages bigint not null default 0,". When you remove those the script works just fine 🙂

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 6

Was the one for sql server 2000 approved? I cannot find it.

¤ §unshine ¤

JamieX Hall of Fame Points: 3763 More actions · Answer 7

both scripts (2000 & 2005) run perfectly. This is a GOOD ONE.

Thanks!!

smartin-756027 SSC Enthusiast Points: 145 More actions · Answer 8

I have backed into a DBA role from a development background an I wonder why there isn't more discussion of [ Microsoft specific I think] SMO object model that affords you the ability to get row count and size of table plus more C# version of this.

Down side of this is it is outside the database [Using a database API though] and there is extremely little documentation more hacking around with it to find what is required unless someone knows of a good source[ The most that I have found is a book with three chapters on it that I have on order]

Certainly having a TSQL script to pull up in a database is probably the norm for DBA's but there could be occasion to write an application or service that may do this on demand or check against a predefined thresh hold and report to you when records exceed a certain predefined number though once again this could be done with a DTS package too

using System.Data.SqlClient;

using Microsoft.SqlServer.Management;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Server;

using Microsoft.SqlServer.Management.Smo;

con.ConnectionString = @ConnectStringSA;

con.Open();

ServerConnection conn = new ServerConnection(con);

Server svr = new Server(conn);

foreach (Table oTable in svr.Databases[@DBName].Tables)

{

string sRowCount = oTable.RowCount.ToString();

string sDataSpace = oTable.DataSpaceUsed.ToString();

\\Yada Yada ya

}

Has anyone else used SMO extensively?

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 9

I have written a short article regarding this.

Please check my blog and provide me your valuable feedback.

http://venkattechnicalblog.blogspot.com/2008/09/list-out-all-tables-and-row-count-in.html

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

sandeep.cs3 Old Hand Points: 307 More actions · Answer 10

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 SSC-Insane Points: 21661 More actions · Answer 11

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 SSC-Insane Points: 21661 More actions · Answer 12

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!