April 5, 2014 at 9:44 am
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.
April 5, 2014 at 10:10 pm
Hi Experts,
Anybody can help?
Thanks.
April 5, 2014 at 11:56 pm
April 6, 2014 at 4:29 am
Hi Eirikur,
Thanks for your response.
I've checked the given URL and it's not what I'm looking for as it's checking for all tables.
What I want is to check for certain tables only as mentioned above.
Anybody else could help out?
Thanks.
April 6, 2014 at 4:38 am
Peter2012 (4/6/2014)
Hi Eirikur,Thanks for your response.
I've checked the given URL and it's not what I'm looking for as it's checking for all tables.
What I want is to check for certain tables only as mentioned above.
Anybody else could help out?
Thanks.
Simply filter the initial selection query to the items you want to include!
This is the a snip from the article:
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id )
/* Add the filter here */
WHERE t1.name IN ('TABLE_1','TABLE_2','TABLE_3');
😎
April 6, 2014 at 5:39 am
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.
April 6, 2014 at 6:13 am
Hi Eirikur,
Thanks for your response and help.
Should it be something like this statement?
INSERT INTO @tab1
SELECT t1.name, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id )
EXEC sp_MSforeachtable @command1="exec sp_spaceused ?;"
WHERE t1.name IN ('TABLE_1','TABLE_2','TABLE_3');
Thanks.
April 6, 2014 at 6:43 am
If you only have few tables to check, do not use sp_MSforeachtable, it executes the statement once for each table in the database, regardless of what ever filtering you use.
The code below gathers the "space used" information, converts KB to MB and displays the results.
😎
declare @TableSpace table
(
TableName sysname
, RowsK varchar(32)
, ReservedMB varchar(32)
, DataMB varchar(32)
, IndexSizeMB varchar(32)
, UnusedMB varchar(32))
/* one row for each table*/
insert @TableSpace exec sp_spaceused 'QUE.EMP001' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP002' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP003' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP004' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP005' ;
SELECT
TableName
,RowsK /* This is the row count! */
,ReservedMB = CAST(REPLACE(ReservedMB ,' KB','') AS FLOAT)/1024
,DataMB = CAST(REPLACE(DataMB ,' KB','') AS FLOAT)/1024
,IndexSizeMB = CAST(REPLACE(IndexSizeMB ,' KB','') AS FLOAT)/1024
,UnusedMB = CAST(REPLACE(UnusedMB ,' KB','') AS FLOAT)/1024
FROM @TableSpace;
go
April 6, 2014 at 9:05 am
Hi Eirikur,
Thanks for your response and help.
It works fantastic, really appreciate for your kind help.
I'll need to perform further testing with other sample data.
Thanks.
April 8, 2014 at 1:49 am
Hi Eirikur,
declare @TableSpace table
(
TableName sysname
, RowsK varchar(32)
, ReservedMB varchar(32)
, DataMB varchar(32)
, IndexSizeMB varchar(32)
, UnusedMB varchar(32))
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.
April 8, 2014 at 2:33 am
Peter2012 (4/8/2014)
Hi Eirikur,
declare @TableSpace table
(
TableName sysname
, RowsK varchar(32)
, ReservedMB varchar(32)
, DataMB varchar(32)
, IndexSizeMB varchar(32)
, UnusedMB varchar(32))
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.
This is a table variable, a form of a temporary table, which is automatically dropped when it goes out of scope. This way you don't have to drop it yourself.
Wayne Sheffield did an excellent article on this: Comparing Table Variables with Temporary Tables[/url]
April 8, 2014 at 9:52 am
Hi Eirikur,
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.
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))
/* one row for each table*/
insert @TableSpace exec sp_spaceused 'QUE.EMP001' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP002' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP003' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP004' ;
insert @TableSpace exec sp_spaceused 'QUE.EMP005' ;
SELECT
TableName
,RowsK /* This is the row count! */
,ReservedMB = CAST(REPLACE(ReservedMB ,' KB','') AS FLOAT)/1024
,DataMB = CAST(REPLACE(DataMB ,' KB','') AS FLOAT)/1024
,IndexSizeMB = CAST(REPLACE(IndexSizeMB ,' KB','') AS FLOAT)/1024
,UnusedMB = CAST(REPLACE(UnusedMB ,' KB','') AS FLOAT)/1024
FROM @TableSpace;
go
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.
April 8, 2014 at 11:42 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply