NEED Query to Find out all Databases sizes

Hi,
I want to find out the SQL databases total size in SQL 2000 server, We have
around 55 databases on the server. Can anobody have the query or Stored proc
to find out the total space used in the server. Thanks
RBif exists (select * from sysobjects where id =
object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO
create procedure CalcSpace
/ ****************************************
********************************/
/* Stored Procedure: CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* */
/* Purpose: <purpose of the script> */
/* A procedure to estimate the disk space requirements of a table. */
/* Refer to Books OnLine topic "Estimating the size of a table..." */
/* for a detailed description */
/* */
/* Input Parameters: <list any input parameters> */
/* @.table_name VARCHAR(30) Name of table to estimate */
/* @.num_rows INT Number of rows in the table */
/* */
/* Output Parameters: <list any output parameters> */
/* - */
/* */
/* Return Status: <list any return codes> */
/* - */
/* */
/* Usage: <a sample usage statement> */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: <other info for this SP> */
/* The is a direct copy from the CalcSpace stored procedure made by*/
/* Sharon Dooley, 1999-04-11. The only change is the added */
/* documentation header and a small bug fix mentioned below. */
/* */
/* Updates: <this section is used to track changes to the script> */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed @.sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/ ****************************************
********************************/
(@.table_name varchar(30)=null,-- name of table to estimate
@.num_rows int = 0) -- number of rows in the table
as
declare @.msg varchar(120)
-- Give usage statement if @.table_name is null
if @.table_name = null or @.num_rows = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end
declare @.num_fixed_col int,
@.fixed_data_size int,
@.num_variable_col int,
@.max_var_size int,
@.null_bitmap int,
@.variable_data_size int,
@.table_id int,
@.num_pages int,
@.table_size_in_bytes int,
@.table_size_in_meg real,
@.table_size_in_kbytes real,
@.sysstat int,
@.row_size int,
@.rows_per_page int,
@.free_rows_per_page int,
@.fillfactor int,
@.num_fixed_ckey_cols int,
@.fixed_ckey_size int,
@.num_variable_ckey_cols int,
@.max_var_ckey_size int,
@.cindex_null_bitmap int,
@.variable_ckey_size int,
@.cindex_row_size int,
@.cindex_rows_per_page int,
@.data_space_used int,
@.num_pages_clevel_0 int,
@.num_pages_clevel_1 int,
@.num_pages_clevel_x int,
@.num_pages_clevel_y int,
@.Num_CIndex_Pages int,
@.clustered_index_size_in_bytes int,
@.num_fixed_key_cols int,
@.fixed_key_size int,
@.num_variable_key_cols int,
@.max_var_key_size int,
@.index_null_bitmap int,
@.variable_key_size int,
@.nl_index_row_size int,
@.nl_index_rows_per_page int,
@.index_row_size int,
@.index_rows_per_page int,
@.free_index_rows_per_page int,
@.num_pages_level_0 int,
@.num_pages_level_1 int,
@.num_pages_level_x int,
@.num_pages_level_y int,
@.num_index_pages int,
@.nonclustered_index_size int,
@.total_num_nonclustered_index_pages int,
@.free_cindex_rows_per_page int,
@.tot_pages int
-- initialize variables
select @.num_fixed_col =0,
@.fixed_data_size =0,
@.num_variable_col =0,
@.max_var_size =0,
@.null_bitmap =0,
@.variable_data_size =0,
@.table_id =0,
@.num_pages =0,
@.table_size_in_bytes =0,
@.table_size_in_meg =0,
@.table_size_in_kbytes =0,
@.sysstat =0,
@.row_size =0,
@.rows_per_page =0,
@.num_fixed_ckey_cols =0,
@.fixed_ckey_size =0,
@.num_variable_ckey_cols =0,
@.max_var_ckey_size =0,
@.cindex_null_bitmap =0,
@.variable_ckey_size =0,
@.cindex_row_size =0,
@.cindex_rows_per_page =0,
@.data_space_used =0,
@.num_pages_clevel_0 =0,
@.num_pages_clevel_1 =0,
@.Num_CIndex_Pages =0,
@.clustered_index_size_in_bytes =0,
@.num_fixed_key_cols =0,
@.fixed_key_size =0,
@.num_variable_key_cols =0,
@.max_var_key_size =0,
@.index_null_bitmap =0,
@.variable_key_size =0,
@.nl_index_row_size =0,
@.nl_index_rows_per_page =0,
@.index_row_size =0,
@.index_rows_per_page =0,
@.free_index_rows_per_page =0,
@.num_pages_level_0 =0,
@.num_pages_level_1 =0,
@.num_pages_level_x =0,
@.num_pages_level_y =0,
@.num_index_pages =0,
@.nonclustered_index_size =0,
@.total_num_nonclustered_index_pages =0,
@.free_cindex_rows_per_page =0,
@.tot_pages =0
set nocount on
-- ****************************************
*****
-- MAKE SURE TABLE EXISTS
-- ****************************************
*****
select @.sysstat = sysstat,
@.table_id = id
from sysobjects where name = @.table_name
if @.sysstat & 7 not in (1,3)
begin
select @.msg = 'I can''t find the table '+@.table_name
print @.msg
return
end
-- ****************************************
*****
-- ESTIMATE SIZE OF TABLE
-- ****************************************
*****
-- get total number and total size of fixed-length columns
select @.num_fixed_col = count(name),
@.fixed_data_size = sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
if @.num_fixed_col= 0 --@.fixed_data_size is null. change to 0
select @.fixed_data_size=0
-- get total number and total maximum size of variable-length columns
select @.num_variable_col=count(name),
@.max_var_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
if @.num_variable_col= 0 --@.max_var_size is null. change to 0
select @.max_var_size=0
-- get portion of the row used to manage column nullability
select @.null_bitmap=2+((@.num_fixed_col+7)/8)
-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @.num_variable_col = 0
select @.variable_data_size=0
else
select @.variable_data_size = 2 + (@.num_variable_col *2 )+
@.max_var_size
-- get row size
select @.row_size= @.fixed_data_size +
@.variable_data_size +
@.null_bitmap + 4 -- 4 represents the data row header
-- get number of rows per page
select @.rows_per_page = (8096) / (@.row_size+2)
-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.
select @.fillfactor = 100 -- initialize it to the maximum
select @.free_rows_per_page = 0 --initialize to no free rows/page
select @.fillfactor=OrigFillFactor
from sysindexes
where id = @.table_id and indid=1 -- indid of 1 means the index is
clustered
if @.fillfactor<>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select @.free_rows_per_page=8096 * ((100-@.fillfactor)/100)/@.row_size
-- get number of pages needed to store all rows
select @.num_pages = ceiling(convert(dec,@.num_rows) /
(@.rows_per_page-@.free_rows_per_page))
-- get storage needed for table data
select @.data_space_used=8192*@.num_pages
-- ****************************************
*****
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
-- ****************************************
*****
-- create a temporary table to contain columns in clustered index.
System table
-- sysindexkeys has a list of the column numbers contained in the index
select colid into #col_list
from sysindexkeys where id= @.table_id and indid=1 -- indid=1 means
clustered
if (select count(*) from #col_list) >0 -- do the following only if
clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in
clustered index
select @.num_fixed_ckey_cols=count(name),
@.fixed_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)
if @.num_fixed_ckey_cols= 0 --@.fixed_ckey_size is null. change to 0
select @.fixed_ckey_size=0
-- get total number and total maximum size of variable-length columns
in clustered index
select @.num_variable_ckey_cols=count(name),
@.max_var_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)
if @.num_variable_ckey_cols= 0 --@.max_var_ckey_size is null. change to
0
select @.max_var_ckey_size=0
-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_ckey_cols <> 0
select @.cindex_null_bitmap=2+((@.num_fixed_ckey_
cols + 7)/8)
else
select @.cindex_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_ckey_cols <> 0
select @.variable_ckey_size=2+(@.num_variable_cke
y_cols
*2)+@.max_var_ckey_size
else
select @.variable_ckey_size=0
-- Calculate the index row size
select @.cindex_row_size=@.fixed_ckey_size
+@.variable_ckey_size+@.cindex_null_bitmap
+1+8
--Next, calculate the number of index rows per page (8096 free bytes
per page):
select @.cindex_rows_per_page=(8096)/(@.cindex_row_size+2)
-- consider fillfactor
if @.fillfactor=0
select @.free_cindex_rows_per_page = 2
else
select @.free_cindex_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.cindex_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_clevel_0=ceiling(convert(deci
mal,(@.data_space_used/8192))/(@.cinde
x_rows_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.num_pages_clevel_0
select @.num_pages_clevel_x=@.num_pages_clevel_0
while @.num_pages_clevel_x <> 1
begin
select
@.num_pages_clevel_y=ceiling(convert(deci
mal,@.num_pages_clevel_x)/(@.cindex_ro
ws_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.Num_CIndex_Pages+@.num
_pages_clevel_y
select @.num_pages_clevel_x=@.num_pages_clevel_y
end
end
-- ****************************************
*****
-- END CLUSTERED INDEX SECTION
-- ****************************************
*****
-- ****************************************
*****
-- BEGIN NON-CLUSTERED INDEX SECTION
-- ****************************************
*****
-- create temp table with non-clustered index info
select indid, colid into #col_list2
from sysindexkeys where id= @.table_id and indid<>1 -- indid=1 means
clustered
if (select count(*) from #col_list2) >0 -- do the following only if
non-clustered indexes exsist
begin
declare @.i int -- a counter variable
select @.i=1 -- initilize to 2, because 1 is id of clustered index
while @.i< 249 -- max number of non-clustered indexes
begin
select @.i=@.i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select @.num_fixed_key_cols = 0,
@.fixed_key_size = 0,
@.num_variable_key_cols = 0,
@.max_var_key_size = 0,
@.index_null_bitmap = 0,
@.variable_key_size = 0,
@.nl_index_row_size = 0,
@.nl_index_rows_per_page = 0,
@.index_row_size = 0,
@.index_rows_per_page = 0,
@.free_index_rows_per_page = 0,
@.num_pages_level_0 = 0,
@.num_pages_level_x = 0,
@.num_pages_level_y = 0,
@.Num_Index_Pages = 0
-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select @.num_fixed_key_cols=count(name),
@.fixed_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_fixed_key_cols= 0 --@.fixed_key_size is null. change to 0
select @.fixed_key_size=0
-- get total number and total maximum size of variable-length columns
in index
select @.num_variable_key_cols=count(name),
@.max_var_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_variable_key_cols= 0 --@.max_var_key_size is null. change to
0
select @.max_var_key_size=0
if @.num_fixed_key_cols = 0 and @.num_variable_key_cols = 0 --there is
no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_key_cols <> 0
select @.index_null_bitmap=2+((@.num_fixed_key_co
ls + 7)/8)
else
select @.index_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_key_cols <> 0
select @.variable_key_size=2+(@.num_variable_key_
cols
*2)+@.max_var_key_size
else
select @.variable_key_size=0
-- Calculate the non-leaf index row size
select @.nl_index_row_size=@.fixed_key_size
+@.variable_key_size+@.index_null_bitmap+1
+8
--Next, calculate the number of non-leaf index rows per page (8096
free bytes per page):
select @.nl_index_rows_per_page=(8096)/(@.nl_index_row_size+2)
-- Next, calculate the leaf index row size
select @.index_row_size=@.cindex_row_size + @.fixed_key_size +
@.variable_key_size+@.index_null_bitmap+1
-- Next, calculate the number of leaf level index rows per page
select @.index_rows_per_page = 8096/(@.index_row_size + 2)
-- Next, calcuate the number of reserved free index rows/page based
on fill factor
if @.fillfactor=0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select @.free_index_rows_per_page=0
else
select @.free_index_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.index_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_level_0=ceiling(convert(decim
al,@.num_rows)/@.index_rows_per_page-@.
free_index_rows_per_page)
select @.Num_Index_Pages=@.num_pages_level_0
select @.num_pages_level_x=@.num_pages_level_0
while @.num_pages_level_x <> 1
begin
select
@.num_pages_level_y=ceiling(convert(decim
al,@.num_pages_level_x)/@.nl_index_row
s_per_page)
select @.Num_Index_Pages=@.Num_Index_Pages+@.num_p
ages_level_y
select @.num_pages_level_x=@.num_pages_level_y
end
select
@.total_num_nonclustered_index_pages=@.tot
al_num_nonclustered_index_pages+@.Num
_Index_Pages
end
end
-- ****************************************
*****
-- END NON-CLUSTERED INDEX SECTION
-- ****************************************
*****
-- display numbers
select @.tot_pages=@.num_pages + @.Num_CIndex_Pages +
@.total_num_nonclustered_index_pages
select @.table_size_in_bytes= 8192*@.tot_pages
select @.table_size_in_kbytes= @.table_size_in_bytes/1024.0
select @.table_size_in_meg= str(@.table_size_in_kbytes/1000.0,17,2)
select substring(@.table_name,1,20) as 'Table Name',
convert(varchar(10),@.table_size_in_meg) as 'MB Estimate',
@.tot_pages as 'Total Pages',
@.num_pages as '#Data Pgs',
@.Num_CIndex_Pages as '#Clustered Idx Pgs',
@.total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'|||email me and i'll send you the text file...|||I thought CalcSpace was a cool proc, so just for kicks, I thought I would
try it on an existing table.
The table has 7million rows and currently consumes (with indexes) 4.5gb.
The script CalcSpace estimated it would need 510mb... I didn't
investigate why this returned such a low number, or why my table is
consuming such a high number (when compared to the estimate)
Here's another method that might work for you. It uses 2 existing system
procedures. It's a little backwards, converting the strings to int's, but
it works.
Create table #tblSize (name sysname,rows int,reserved varchar(10),data
varchar(10),idx varchar(10),used varchar(10))
insert into #tblSize EXEC sp_MSforeachtable @.command1='sp_spaceused ''?'''
-- select * from #tblSize order by rows desc
select sum(cast(substring(reserved,1,charindex(
'KB',data,1)-1) as
int) ),sum(cast(substring(data,1,charindex('K
B',data,1)-1) as int) +
cast(substring(idx,1,charindex('KB',idx,
1)-1) as int) ) as used
from #tblSize
Bill
"rb" <srbssr@.yahoo.com> wrote in message
news:eswxO7D9EHA.368@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to find out the SQL databases total size in SQL 2000 server, We
> have
> around 55 databases on the server. Can anobody have the query or Stored
> proc
> to find out the total space used in the server. Thanks
> RB
>
>