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
RB
if 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_nameVARCHAR(30)Name of table to estimate*/
/*@.num_rowsINTNumber 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-04Magnus AnderssonChanged @.sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation.*/
/* */
/************************************************** **********************/
(@.table_namevarchar(30)=null,-- name of table to estimate
@.num_rowsint = 0) -- number of rows in the table
as
declare @.msgvarchar(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_colint,
@.fixed_data_sizeint,
@.num_variable_colint,
@.max_var_sizeint,
@.null_bitmapint,
@.variable_data_sizeint,
@.table_idint,
@.num_pagesint,
@.table_size_in_bytesint,
@.table_size_in_megreal,
@.table_size_in_kbytesreal,
@.sysstatint,
@.row_sizeint,
@.rows_per_pageint,
@.free_rows_per_pageint,
@.fillfactorint,
@.num_fixed_ckey_cols int,
@.fixed_ckey_size int,
@.num_variable_ckey_cols int,
@.max_var_ckey_size int,
@.cindex_null_bitmapint,
@.variable_ckey_sizeint,
@.cindex_row_sizeint,
@.cindex_rows_per_pageint,
@.data_space_usedint,
@.num_pages_clevel_0int,
@.num_pages_clevel_1int,
@.num_pages_clevel_xint,
@.num_pages_clevel_yint,
@.Num_CIndex_Pagesint,
@.clustered_index_size_in_bytesint,
@.num_fixed_key_cols int,
@.fixed_key_size int,
@.num_variable_key_cols int,
@.max_var_key_size int,
@.index_null_bitmapint,
@.variable_key_sizeint,
@.nl_index_row_sizeint,
@.nl_index_rows_per_pageint,
@.index_row_sizeint,
@.index_rows_per_pageint,
@.free_index_rows_per_pageint,
@.num_pages_level_0int,
@.num_pages_level_1int,
@.num_pages_level_xint,
@.num_pages_level_yint,
@.num_index_pagesint,
@.nonclustered_index_sizeint,
@.total_num_nonclustered_index_pagesint,
@.free_cindex_rows_per_pageint,
@.tot_pagesint
-- 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_ckey_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(decimal,(@.data _space_used/8192))/(@.cindex_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(decimal,@.num_p ages_clevel_x)/(@.cindex_rows_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.Num_CIndex_Pages+@.num_pages_cle vel_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_cols + 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(decimal,@.num_ro ws)/@.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(decimal,@.num_pa ges_level_x)/@.nl_index_rows_per_page)
select @.Num_Index_Pages=@.Num_Index_Pages+@.num_pages_level _y
select @.num_pages_level_x=@.num_pages_level_y
end
select
@.total_num_nonclustered_index_pages=@.total_num_non clustered_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('KB',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
>
>