Table Size Query

[TheLittleSeeker]

Hi Experts,

I'm using Ms SQL Server 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.

 

[Olaf Doschke]

Well, you have to use sp_MSforeachtable in a way to have the different table names in the command executed, instead of your constant name 'QUE.EMP001'.
What you do now is executing the same command 5 times, if there are 5 tables.

It's a bit difficult to use an undocumented function, as it is - well - undocumented.
But see what I found:

http://www.databasejournal.com/feat...edures-spMSforeachtable-and-spMSforeachdb.htm

So use ? instead of 'QUE.EMP001' and the command will be executed with the different table names:

insert @TableSpace
exec sp_MSforeachtable @command1="exec sp_spaceused ?;"

By the way it's also not hard to determine table names, filed names etc. from INFORMATION_SCHEMA views. See

http://www.mssqltips.com/sqlservertutorial/179/sql-server-informationschema-views/

Bye, Olaf.

 

[Olaf Doschke]

more precise:

insert into @tablespace
exec sp_MSforeachtable @command1="exec sp_spaceused '?';"

1. Insert SQL needs INTO. INSERT INTO tablename, SELECT FROM tablename, UPDATE tablename.
2. The ? needs to be put into quotes as the table name is used as parameter of another stored procedure sp_spaceused. My mistake.

Bye, Olaf.

 

[TheLittleSeeker]

Hi Olaf,

Thanks for your response and help.

Could you help on how can I use commands "sp_MSforeachtable" and "sp_spaceused" to check only for these selected tables: EMP001, EMP002, EMP003, EMP004, EMP005 ?

Appreciate for your help.

Thanks.

 

[Olaf Doschke]

sp_MSforeachtable loop all tables. Again, as siad, it's undocumented, so I can't say how it could be used to limit the list.
On the other side having a table list makes this easy as can be by not using the sp_MSforeachtable procedure at all, doesn't it?

insert into @tablespace
exec sp_spaceused 'EMP001'

insert into @tablespace
exec sp_spaceused 'EMP002'

insert into @tablespace
exec sp_spaceused 'EMP003'

insert into @tablespace
exec sp_spaceused 'EMP004'

insert into @tablespace
exec sp_spaceused 'EMP005'

Bye, Olaf.

 

[Olaf Doschke]

Or you take the total result and delete all records not about the EMP tables:

delete from @TableSpace where TableName not like 'EMP%'

After doing the insert, obviously.

Bye, Olaf.

 

[TheLittleSeeker]

Hi Olaf,

Thanks for your response and help.

I've tried something like this, but it does not working.

declare @tablespace table (TableName sysname, RowsK varchar(32), ReservedMB varchar(32), DataMB varchar(32), IndexSizeMB varchar(32), UnusedMB varchar(32))

insert into @tablespace 
exec sp_spaceused 'QUE.EMP001'

insert into @tablespace 
exec sp_spaceused 'QUE.EMP002'

insert into @tablespace 
exec sp_spaceused 'QUE.EMP003'

insert into @tablespace 
exec sp_spaceused 'QUE.EMP004'

insert into @tablespace 
exec sp_spaceused 'QUE.EMP005'

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

drop table @tablespace

Did I miss out anything?

Appreciate for your help.

Thanks.

 

[Olaf Doschke]

Sorry, I neither can read your mind nor screen. What message do you receive, when you execute this in a query window in sql server management studio?

Bye, Olaf.

 

[Olaf Doschke]

insert into @tablespace
exec sp_MSforeachtable @command1="exec sp_spaceused '?';"

If this worked for you, it would be much easier to limit the result to EMP tables by adding the other solution to delete unwanted rows, wouldn't it?

Besides this works with Adventureworks2012 on SQL2012:

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

Insert into @TableSpace
exec sp_spaceused 'Production.BillOfMaterials';

Select * from @TableSpace

So in general just using sp_spaceused with a full qualified table name works.

Bye, Olaf.

 

[TheLittleSeeker]

Hi Olaf,

Thanks for your response and help.

I'll do further testing with your method.

Thanks.

 

[TheLittleSeeker]

Hi Olaf,

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

Insert into @TableSpace
exec sp_spaceused 'Production.BillOfMaterials';

Select * from @TableSpace

Can I check what does "declare" do?

Is this something like temporary variable?

I saw another example from internet whereby they use a temporary table to store these values, then later drop the table.

Could you advice?

Thanks.

 

[Olaf Doschke]

DECLARE declares variables. A Table Variable indeed compares to a temporary table, but it's a variable and exists with the scope of the variable, you don't need to drop it.

http://technet.microsoft.com/de-de/library/ms188927.aspx

Bye, Olaf.

 

[TheLittleSeeker]

Hi Olaf,

Thank you very much for your response and help.

It had cleared my doubt and the given article is very useful.

If it's not too much too ask, would like to check on the following 2 items:

1) Would it be possible to have below sql statement (SAMPLE 2) coded into SQLCMD command and batch script? I wanted to create dos batch job with this SQL statements (SAMPLE 1). I don't want to use it by calling an additional .sql file, I wanted to embedded these SQL statements into my dos batch script so it'll be protected. Hope I've explained it clearly. I could not use the method in SAMPLE 1 because of long SQL statements in SAMPLE 2.

SAMPLE 1

SET SQL="SET NOCOUNT ON;SELECT col1,col2,col3 FROM t1 JOIN t2 ON (t1.col4=t2.col2) WHERE (t2.col1 LIKE '%%Wildcard goes here%%') AND (t1.col5 >= '%ydate3%') ORDER BY col1 ASC"

call sqlcmd -S server\s1 -U sa -P pw -d DB -Q %SQL% -o output.txt -u -n -s "" -w180

SAMPLE 2

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

Insert into @TableSpace
exec sp_spaceused 'Production.BillOfMaterials';

Select * from @TableSpace

2) If I wanted to generate an output of these SQL statements (SAMPLE 2) into a text file, will it be possible to format its output and its alignment so it'll be more presentable and readable?

Thank you.

 

[TheLittleSeeker]

Hi All,

I've managed to solve the questions that I've posted earlier.

Thank you for your help and support.

Have a nice day.

Bye