need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

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
>
>

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
>
>

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_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_pages_clevel_x)/(@.cindex_rows_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_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_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(decimal,@.num_pages_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_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('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
>
>

Need help! Used Space in dB and Tran files

I have a database about 39GB in size and is running out of allocated space. In the Enterprise Manager the properties for this dB, show that there is 0.00MB space available. I need to increase the size of the dB.

What I need to find out is, which file group is full so I can expand the appropriate file group instead of expanding all the filegroups. Also, is there a way to find the space usage of each file in the file group and also the Transaction log?

db Size 39GB
14 File Groups
17 database files (mdf & ndf)
2 Transaction Log files
180 Tables

I am aware of the sp_spaceused, but the information it furnishes is not enough for me to make a decision.

Need help urgently!!!!check this query out and see if this will be of any help:

select [Allocated_Size_MB]=size*8/1024, [Max_Size_MB]=maxsize*8/1024, name from sysfiles|||Thanks for your reply. The results from the query give me the allocated size.
How can I find the "used" space (or free/available space) in this allocated space?