August 10, 2006 at 2:23 am
If I have a database with a list of tables is there a way to calculate the size of each table individually and
then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the
columns are of variable length you could do something like
( column1width + column2width + column3width ) * No.of Rows = Tablesize
So my question is can I reference the column width of different columns in a
table using sql ?
August 10, 2006 at 1:43 pm
there's a difference between actual space used, and max space;
this script i'm pasting below will give you the actual space used;
go
/*******************************************************************************
Written By : Simon Sabin
Date : 25 October 2002
Description : Returns the spaceused by all tables in a database
:
History
Date Change
------------------------------------------------------------------------------
25/10/2002 Created
*******************************************************************************/
SET NOCOUNT ON
DECLARE @SetOption bit, @databasename varchar(30), @orderCol varchar(30), @numeric bit
/*******************************************************************************
--Change this to change the way data is ordered
*******************************************************************************/
SELECT @orderCol = 'data'
SELECT @DatabaseName = db_name()
SELECT @numeric = 1
IF @DatabaseName <> 'Master'
AND NOT EXISTS (select 1 from master..sysdatabases WHERE name = @DatabaseName AND (status & 4) = 4)
BEGIN
exec sp_dboption @databaseName ,'select into/bulkcopy', 'true'
SELECT @SetOption = 1
END
IF EXISTS (SELECT 1 FROM master..sysobjects WHERE name = 'space1')
DROP TABLE master..space1
CREATE TABLE master..Space1 (name varchar(60), rows varchar(11), reserved varchar(11), data varchar(11), index_size varchar(11), unused varchar(11))
DECLARE @Cmd varchar(255)
declare cSpace CURSOR FOR
select 'USE ' +@DatabaseName + ' INSERT into master..space1 EXEC sp_spaceUsed ''[' + u.name + '].[' + o.name + ']'''
FROM sysobjects o
join sysusers u on u.uid = o.uid
WHERE type = 'U'
AND o.Name <> 'Space1'
OPEN cSPACE
FETCH cSpace INTO @Cmd
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT @Cmd
EXECUTE (@Cmd)
FETCH cSpace INTO @Cmd
END
DEALLOCATE cSPace
SELECT Description,
Rows,
Reserved,
Data,
Index_size,
dataPerRows
FROM (
SELECT 3 DataOrder,
CONVERT(int,CASE @OrderCol WHEN 'Rows' THEN Rows
WHEN 'Reserved' THEN SUBSTRING(Reserved, 1,LEN(Reserved)-2)
WHEN 'data' THEN SUBSTRING(Data, 1,LEN(Data)-2)
WHEN 'index_size' THEN SUBSTRING(Index_size, 1,LEN(index_Size)-2)
WHEN 'unused' THEN SUBSTRING(unused, 1,LEN(unused)-2) END) OrderData,
name Description,
rows,
CASE @NUMERIC WHEN 0 THEN reserved ELSE SUBSTRING(reserved, 1, len(reserved)-2) END reserved,
CASE @NUMERIC WHEN 0 THEN data ELSE SUBSTRING(data, 1, len(data)-2) END data,
CASE @NUMERIC WHEN 0 THEN index_size ELSE SUBSTRING(index_size, 1, len(index_size)-2) END index_size,
--SUBSTRING(data, 1, len(data)-2) DataPerRows
--CONVERT(numeric(19,6),SUBSTRING(data, 1, len(data)-2)) /rows dataPerRows
CASE WHEN Rows = 0 THEN '' ELSE CONVERT(varchar(11),CONVERT(numeric(10,2),CONVERT(numeric,SUBSTRING(reserved, 1, len(reserved)-2)) /rows*1000)) END DataPerRows
FROM master..Space1
UNION ALL
SELECT 1 DataOrder, 0 OrderData,
CONVERT(varchar(30),'Total' ) Description,
CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
''
FROM master..space1
UNION ALL
SELECT 2, 0,
REPLICATE('-',30),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11)
UNION ALL
SELECT 4,0,
REPLICATE('-',30),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11)
UNION ALL
SELECT 5,0,
CONVERT(varchar(30),'Total' ) Description,
CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
''
FROM master..space1 ) Stuff
ORDER BY DataOrder, OrderData desc, description
EXECUTE ('DROP TABLE master..space1')
IF @SetOption = 1
exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
GO
Lowell
August 11, 2006 at 11:47 am
"rockmoose" posted an excellet script at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=119721
Nice and short and does the job:
/*=========================================================================
Title: Table Sizes
Script rockmoosesTableSizes.sql
Purpose: Get top 10 tables sizes (disk) space by reserved space
Create/Change History:
6/8/2004 10:54:00 AM -- rockmoose: Posted on SQL Server Central forum
Note:
source: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=119721
=========================================================================*/
create table #table_size(name nvarchar(60),
rows int,
reserved_kb varchar(18),
data_kb varchar(18),
index_kb varchar(18),
unused_kb varchar(18) )
insert #table_size exec sp_MsForeachTable 'exec sp_spaceused ''?''--,true'
-- "uncomment" --,true above to run updateusage
select top 10
name,
rows,
convert(int,replace(reserved_kb,' KB','')) as reserved_kb,
convert(int,replace(data_kb,' KB','')) as data_kb,
convert(int,replace(index_kb,' KB','')) as index_kb,
convert(int,replace(unused_kb,' KB','')) as unused_kb
from
#table_size
order by
rows desc
-- reserved_kb desc
drop table #table_size
G. Milner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply