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 Saturday, March 08, 2008 5:30 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:51 AM
Points: 711, Visits: 214
Comments posted to this topic are about the item Row count and space used for all tables in database
Post #466335
Posted Friday, April 18, 2008 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 06, 2010 1:23 AM
Points: 30, Visits: 163
Very powerful script. Do you have the SQL Server 2000 version of this script?

Thank you,
GT
Post #487297
Posted Monday, April 21, 2008 1:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:51 AM
Points: 711, Visits: 214
There has been added a version of this procedure for SQL Server 2000.
It is waiting for approval now.
Post #487740
Posted Sunday, May 04, 2008 9:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 1:35 PM
Points: 116, Visits: 330
Maybe add

order by cat_name,sch_name,tab_name

for the select statement from the temp table.
Post #494766
Posted Friday, May 09, 2008 3:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 5:13 AM
Points: 25, Visits: 35
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. :D

Will be glad if you replied on its worth!!! :)
Thanks,
Khilan
Post #497684
Posted Tuesday, July 29, 2008 9:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:45 AM
Points: 279, Visits: 362
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 :)
Post #542784
Posted Wednesday, August 20, 2008 1:35 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 11:34 AM
Points: 361, Visits: 1,138
Was the one for sql server 2000 approved? I cannot find it.

¤ §unshine ¤
Post #556072
Posted Monday, August 25, 2008 1:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
Points: 371, Visits: 437
both scripts (2000 & 2005) run perfectly. This is a GOOD ONE.
Thanks!!
Post #558412
Posted Monday, September 15, 2008 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 02, 2014 12:25 PM
Points: 3, Visits: 70
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?
Post #569619
Posted Tuesday, September 16, 2008 2:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 15, 2012 7:06 AM
Points: 122, Visits: 205
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, :P
My Blog:

http://venkattechnicalblog.blogspot.com/
Post #569991
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse