SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Size Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter2012
Starting Member

Australia
9 Posts

Posted - 04/05/2014 :  11:30:10  Show Profile  Reply with Quote
Hi Experts,

I'm using Ms SQL 2008 R2 (Enterprise edition), I need some help in compiling the correct SQL query to check table size of certain tables.

There are plenty of examples from forums on checking for all tables, however, this is not what I'm looking for.

Let's say I've the following tables: EMP001, EMP002, EMP003, EMP004, EMP005 in database schema called: QUE.

Here are some examples that I've found which is what I would like to have it on my SQL coding:

declare @TableSpace table (TableName sysname, RowsK varchar(32), ReservedMB

varchar(32), DataMB varchar(32), IndexSizeMB varchar(32), UnusedMB varchar(32))

insert @TableSpace
exec sp_MSforeachtable @command1="exec sp_spaceused 'QUE.EMP001';"

update @TableSpace set RowsK = CONVERT(varchar, 1+convert(int, RowsK)/1024)

update @TableSpace set ReservedMB = CONVERT(varchar, 1+convert(int,LEFT(ReservedMB,

charindex(' K', ReservedMB,-1)))/1024)

update @TableSpace set DataMB = CONVERT(varchar, 1+convert(int,LEFT(DataMB,

charindex(' K', DataMB,-1)))/1024)

update @TableSpace set IndexSizeMB = CONVERT(varchar, convert(int,LEFT(IndexSizeMB,

charindex(' K', IndexSizeMB,-1)))/1024)

update @TableSpace set UnusedMB = CONVERT(varchar, convert(int,LEFT(UnusedMB,

charindex(' K', UnusedMB,-1)))/1024)


select * from @TableSpace order by convert(int,DataMB) desc
go

Is this the right way of doing so?

The above SQL coding is only checking for 1 table, how can it check for multiple tables, 5 tables?

Could you help how can I make this coding working?

I've tried doing it several times on my own, but could not figure it out so far.

Thank you for your help.


Edited by - Peter2012 on 04/06/2014 00:02:53

Peter2012
Starting Member

Australia
9 Posts

Posted - 04/06/2014 :  00:10:21  Show Profile  Reply with Quote
Hi Experts,

Anybody can help?

Thanks.
Go to Top of Page

Peter2012
Starting Member

Australia
9 Posts

Posted - 04/06/2014 :  07:39:11  Show Profile  Reply with Quote
Hi Experts,

In brief, could anyone help on how to use these commands "sp_MSforeachtable" and "sp_spaceused" to check only for certain tables only, for instance: EMP001, EMP002, EMP003, EMP004, EMP005 ?

Appreciate for your help.

Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.97 seconds. Powered By: Snitz Forums 2000