Table Size Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Table Size Query

Author

Topic

Peter2012
Starting Member

27 Posts

Posted - 2014-04-05 : 11:30:10

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.

Peter2012
Starting Member

27 Posts

Posted - 2014-04-06 : 00:10:21

Hi Experts,

Anybody can help?

Thanks.

Peter2012
Starting Member

27 Posts

Posted - 2014-04-06 : 07:39:11

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources