Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Estimating the table size...


Estimating the table size...

Author
Message
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1652
Comments posted to this topic are about the item Estimating the table size...

Regards,
Sqlfrenzy
William Soranno
William Soranno
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 514
After cleaning up the text of the functions so it would create them, the function fn_GetTableSize only returns NULL.
I changed the function so the calculations handle a NULL return.

ALTER FUNCTION [dbo].[fn_GetTableSize]
(
@TableName VARCHAR(100)
)
RETURNS VARCHAR(25)
AS BEGIN
DECLARE @TableSize VARCHAR(25)
IF EXISTS ( SELECT
1
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(@TableName)
AND type = 1 )
BEGIN
SELECT
@TableSize = ISNULL(dbo.fn_ClusteredIndexSize(@TableName), 0)
+ ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName), 0)
END
ELSE
BEGIN
SELECT
@TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0)
+ ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName),
0))
END
SET @TableSize = LTRIM(STR(@TableSize)) + ' KB'
RETURN @TableSize
END



Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1652
do you mean that it is not working....

Regards,
Sqlfrenzy
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1733 Visits: 461
The uploaded text is full of gargage characters that will prevent a quick copy and paste from compiling.

Any way to clean up the script.

-- Mark D Powell --
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1652
well... I just scripted the function thru ssms copied them to the editor... I will try to remove the characters.....

Regards,
Sqlfrenzy
dant12
dant12
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 711
For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile this

edit:
what version of SQL were you using to write this?

--
Thiago Dantas
@DantHimself
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1652
dant12 (7/2/2009)
For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile this

edit:
what version of SQL were you using to write this?


u r getting the syntax errors because the characters added by the editor.....

Regards,
Sqlfrenzy
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1652
I have attached the updated script...once published it will be available for viewing....

Regards,
Sqlfrenzy
dant12
dant12
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 711
ah, yes.

pasted it in notepad and got to see it

quick replace fixed it

--
Thiago Dantas
@DantHimself
William Soranno
William Soranno
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 514
Here is a cleaned up set of code.
CREATE FUNCTION [dbo].[fn_CalculateHeapSize]
(
@Tablename VARCHAR(100)
)
RETURNS VARCHAR(100)
AS BEGIN
-- Calculate the space used taken at leaf level
DECLARE
@Num_Rows FLOAT
,@Num_Cols INT
,@Fixed_data_size INT
,@Num_var_Cols INT
,@Max_var_size INT
,@Null_Bitmap INT
,@Variable_Data_Size INT
,@Heap_size BIGINT
,@Row_Size INT
,@Rows_per_page FLOAT
,@Num_Pages FLOAT

SET @Num_Rows = (
SELECT
[rows]
FROM
sys.sysindexes
WHERE
indid = 1
AND id = OBJECT_ID(@Tablename)
)

SET @Num_Cols = (
SELECT
COUNT(*)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
)

SET @Fixed_data_size = (
SELECT
SUM(max_lenGth)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id NOT IN (165, 167, 231, 34,
35, 99)
)

SET @Num_var_Cols = (
SELECT
COUNT(*)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id IN (165, 167, 231, 34, 35, 99)
)

SET @Max_var_size = (
SELECT
SUM(max_lenGth)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id IN (165, 167, 231, 34, 35, 99)
)

SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8

IF (@Num_var_Cols = 0)
BEGIN
SET @Variable_Data_Size = 0
END
ELSE
BEGIN
SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
END

SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

SET @Rows_per_page = 8096 / (@Row_Size + 2)

-- No. of pages needed to store rows
SET @Num_Pages = CEILING(@Num_Rows / @Rows_per_page)

SET @Heap_size = (8192 * @Num_Pages) / 1024

-- Space used to store index info
RETURN LTRIM(STR(@Heap_size))-- + ' KB'
END


GO

CREATE FUNCTION [dbo].[fn_ClusteredIndexSize]
(
@Tablename VARCHAR(100)
)
RETURNS BIGINT
AS BEGIN
-- Calculate the space used taken at leaf level
DECLARE
@Num_Rows FLOAT
,@Num_Cols INT
,@Fixed_data_size INT
,@Num_var_Cols INT
,@Max_var_size INT
,@fill_factor INT
,@uniquifier SMALLINT
,@uniquefiersize SMALLINT
,@Null_Bitmap INT
,@Variable_Data_Size INT
,@Total_Space VARCHAR(100)
,@Row_Size INT
,@Rows_per_page FLOAT
,@Free_rows_per_page FLOAT
,@level FLOAT
,@Num_Pages FLOAT
,@Leaf_level_space INT

SET @uniquifier = 1
SET @uniquefiersize = 4
SET @Num_Rows = (
SELECT
[rows]
FROM
sys.sysindexes
WHERE
indid = 1
AND id = OBJECT_ID(@Tablename)
)

SET @Num_Cols = (
SELECT
COUNT(*)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
)

SET @Fixed_data_size = (
SELECT
SUM(max_lenGth)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id NOT IN (165, 167, 231, 34,
35, 99)
)

SET @Num_var_Cols = (
SELECT
COUNT(*)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id IN (165, 167, 231, 34, 35, 99)
)

SET @Max_var_size = (
SELECT
SUM(max_lenGth)
FROM
sys.columns
WHERE
object_id = OBJECT_ID(@Tablename)
AND system_type_id IN (165, 167, 231, 34, 35, 99)
)

IF ((
SELECT
is_unique
FROM
sys.indexes
WHERE
type = 1
AND object_id = OBJECT_ID(@Tablename)
) = 0)
BEGIN
SET @Num_Cols = @Num_Cols + @uniquifier
SET @Num_var_Cols = @Num_var_Cols + @uniquifier
SET @Max_var_size = @Max_var_size + @uniquefiersize
END
SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8

SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size

SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

SET @Rows_per_page = 8096 / (@Row_Size + 2)

SET @fill_factor = (
SELECT
fill_factor
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(@Tablename)
AND type = 1
)

-- No. of reserved free rows per page
SET @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100)
/ (@Row_Size + 2))

-- No. of pages needed to store rows
SET @Num_Pages = CEILING((@Num_Rows / (@Rows_per_page
- @Free_rows_per_page)))

SET @Leaf_level_space = 8192 * @Num_Pages

-- Space used to store index info
DECLARE
@Num_Key_cols INT
,@Fixed_key_size INT
,@Num_var_key_cols INT
,@Max_var_key_size INT
,@Index_Null_Bitmap INT
,@Variable_Key_size INT
,@Index_row_size INT
,@Index_row_per_page FLOAT
,@levels INT
,@Num_Index_pages INT
,@Index_level_space INT
,@Null_Cols INT
SET @Num_Key_cols = (
SELECT
Keycnt
FROM
sys.sysindexes
WHERE
id = OBJECT_ID(@Tablename)
AND indid = 1
)

SET @Fixed_key_size = (
SELECT
SUM(max_length)
FROM
sys.index_columns a
,sys.indexes b
,sys.columns c
WHERE
a.index_id = b.index_id
AND b.object_id = OBJECT_ID(@Tablename)
AND type = 1
AND a.object_id = b.object_id
AND c.object_id = b.object_id
AND a.column_id = c.column_id
AND system_type_id NOT IN (165, 167, 231, 34, 35,
99)
)

SET @Num_var_key_cols = (
SELECT
COUNT(c.name)
FROM
sys.index_columns a
,sys.indexes b
,sys.columns c
WHERE
a.index_id = b.index_id
AND b.object_id = OBJECT_ID(@Tablename)
AND type = 1
AND a.object_id = b.object_id
AND c.object_id = b.object_id
AND a.column_id = c.column_id
AND system_type_id IN (165, 167, 231, 34, 35,
99)
)

SET @Max_var_key_size = (
SELECT
ISNULL(SUM(max_length), 0)
FROM
sys.index_columns a
,sys.indexes b
,sys.columns c
WHERE
a.index_id = b.index_id
AND b.object_id = OBJECT_ID(@Tablename)
AND type = 1
AND a.object_id = b.object_id
AND c.object_id = b.object_id
AND a.column_id = c.column_id
AND system_type_id IN (165, 167, 231, 34, 35,
99)
)

IF ((
SELECT
is_unique
FROM
sys.indexes
WHERE
type = 1
AND object_id = OBJECT_ID(@Tablename)
) = 0)
BEGIN
SET @Num_Key_cols = @Num_Key_cols + @uniquifier
SET @Num_var_key_cols = @Num_var_key_cols + @uniquifier
SET @Max_var_key_size = @Max_var_key_size + @uniquefiersize
END

SET @Null_Cols = (
SELECT
ISNULL(COUNT(c.name), 0)
FROM
sys.index_columns a
,sys.indexes b
,sys.columns c
WHERE
a.index_id = b.index_id
AND b.object_id = OBJECT_ID(@Tablename)
AND type = 1
AND a.object_id = b.object_id
AND c.object_id = b.object_id
AND a.column_id = c.column_id
AND c.is_nullable = 1
)

SELECT
@Index_level_space = dbo.fn_getIndexSpace(@Null_Cols,
@Num_var_key_cols,
@Max_var_key_size,
@Fixed_key_size, @Num_Rows)
SET @Total_space = LTRIM(STR((@Index_level_space + @Leaf_level_space)
/ (1024)))
RETURN @Total_space
END


GO


CREATE FUNCTION [dbo].[fn_getIndexSpace]
(
@Num_Null_key_cols INT
,@Num_var_key_cols INT
,@Max_var_key_size INT
,@Fixed_key_size INT
,@Num_Rows FLOAT
)
RETURNS BIGINT
AS BEGIN
DECLARE
@Index_Null_Bitmap INT
,@Var_Key_Size INT
,@Index_row_Size INT
,@Index_Row_per_Page FLOAT
,@level FLOAT
,@Num_Index_pages INT
,@Index_Space_Used BIGINT

IF (@Num_Null_key_cols > 0)
BEGIN
SET @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols + 7) / 8)
END
ELSE
BEGIN
SET @Index_Null_Bitmap = 0
END

IF (@Num_var_key_cols > 0)
BEGIN
SET @Var_Key_Size = 2 + (@Num_var_key_cols * 2)
+ @Max_var_key_size
END
ELSE
BEGIN
SET @Var_Key_Size = 0
END

SET @Index_row_Size = @Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap
+ 1 + 6
SET @Index_Row_per_Page = 8096 / (@Index_row_Size + 2)
SET @level = 1 + FLOOR(ABS((LOG10(@Num_Rows / @Index_row_per_page)
/ LOG10(@Index_row_per_page))))
SET @Num_Index_pages = 0
DECLARE @i INT
IF (@level > 0)
BEGIN
SET @i = 1
WHILE(@i <= @Level)
BEGIN
SET @Num_Index_pages = @Num_Index_pages
+ POWER(@Index_row_per_page, @level - @i)
SET @i = @i + 1
END
END

SET @Index_Space_Used = (8192 * @Num_Index_pages)
RETURN @Index_Space_Used

END


GO


CREATE FUNCTION [dbo].[fn_GetLeafLevelIndexSpace]
(
@Num_Leaf_Cols INT
,@Num_Var_leaf_Cols INT
,@Max_var_leaf_size INT
,@Fixed_Leaf_Size INT
,@Fill_Factor INT
,@Num_Rows FLOAT
)
RETURNS BIGINT
AS BEGIN

DECLARE
@Leaf_Null_Bitmap INT
,@Variable_leaf_size INT
,@Leaf_Row_Size INT
,@Leaf_Rows_per_page INT
,@Free_Rows_Per_Page INT
,@Num_Leaf_Pages FLOAT
,@Leaf_Space_Used INT

SET @Leaf_Null_Bitmap = 2 + ((@Num_Leaf_Cols + 7) / 8)

IF (@Num_Var_leaf_Cols > 0)
BEGIN
SET @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2)
+ @Max_var_leaf_size
END
ELSE
BEGIN
SET @Variable_leaf_size = 0
END


SET @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size
+ @Leaf_Null_Bitmap + 1 + 6

SET @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2)

SET @Free_Rows_Per_Page = 8096 * (((100 - @Fill_Factor) / 100)
/ (@Leaf_Row_Size + 2))

SET @Num_Leaf_Pages = CEILING((@Num_Rows / (@Leaf_Rows_per_page
- @Free_Rows_Per_Page)))

SET @Leaf_Space_Used = 8192 * @Num_Leaf_Pages

RETURN @Leaf_Space_Used
END


GO


CREATE FUNCTION [dbo].[fn_GetNonClusteredIndexSize]
(
@TableName VARCHAR(100)
)
RETURNS BIGINT
AS BEGIN

DECLARE
@Num_Rows FLOAT
,@Num_Key_cols INT
,@Fixed_key_size INT
,@Num_var_key_cols INT
,@Max_var_key_size INT
,@is_clustered INT
,@index_id INT
,@is_unique BIT
,@Num_Diff_cols INT
,@Num_Null_key_cols INT
,@Num_Index_pages INT
,@Index_Space_Used INT
,@Total_Index_space BIGINT
,@Num_Leaf_Cols INT
,@Num_Included_Cols INT
,@Leaf_Level_Space INT
,@Fill_Factor INT

-- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL
-- No of Rows in a table
SET @Total_Index_space = 0
SET @Leaf_Level_Space = 0
-- insert info intom temp table
SET @Num_Rows = (
SELECT
[rows]
FROM
sys.sysindexes
WHERE
indid = 1
AND id = OBJECT_ID(@TableName)
)
DECLARE @Tmp_Info TABLE
(
Index_id INT
,Num_key_cols INT
,type INT
,is_unique BIT
,is_included SMALLINT
,fill_factor INT
,Num_Var_Key_cols INT
,Fixed_Key_Size INT
,Max_Var_Key_Size INT
)
DECLARE @tmp_Index_info TABLE
(
sno INT IDENTITY(1, 1)
,index_id INT
,Num_key_cols INT
,type INT
,is_unique BIT
,Num_Var_Key_cols INT
,Fixed_Key_Size INT
,Max_Var_Key_Size INT
,Num_Included_Col INT
,fill_factor INT
)
INSERT INTO
@Tmp_Info
SELECT
b.Index_id
,COUNT(c.name) Num_key_cols
,b.type
,b.is_unique
,is_included_column
,fill_factor
,ISNULL((
SELECT
COUNT(c.name)
FROM
sys.columns e
WHERE
e.object_id = c.object_id
AND a.column_id = e.column_id
AND c.system_type_id IN (165, 167, 231, 34, 35, 99)
), 0) AS Num_Var_Key_cols
,ISNULL((
SELECT
SUM(max_length)
FROM
sys.indexes d
WHERE
d.index_id = b.index_id
AND d.object_id = c.object_id
AND c.system_type_id NOT IN (165, 167, 231, 34, 35, 99)
), 0) AS Fixed_Key_Size
,ISNULL((
SELECT
SUM(max_length)
FROM
sys.indexes d
WHERE
d.index_id = b.index_id
AND d.object_id = c.object_id
AND c.system_type_id IN (165, 167, 231, 34, 35, 99)
), 0) AS Max_Var_Key_Size
--into @Tmp_Info
FROM
sys.index_columns a
,sys.columns c
,sys.indexes b
WHERE
a.column_id = c.column_id
AND a.index_id = b.index_id
AND b.object_id = c.object_id
AND a.object_id = b.OBJECT_ID
AND b.object_id = OBJECT_ID(@TableName) --and b.type>1
GROUP BY
c.name
,b.index_id
,c.object_id
,c.system_type_id
,a.column_id
,b.type
,b.is_unique
,is_included_column
,fill_factor
ORDER BY
b.index_id


INSERT INTO
@tmp_Index_info
SELECT
index_id AS Index_id
,SUM(num_key_cols) AS num_key_cols
,type
,is_unique
,SUM(Num_var_key_cols) AS Num_var_key_cols
,SUM(Fixed_key_size) AS Fixed_key_size
,SUM(max_var_key_size) AS max_var_key_size
,SUM(is_included)
,fill_factor
--into @tmp_Index_info
FROM
@Tmp_Info
WHERE
type > 1
GROUP BY
index_id
,type
,is_unique
,fill_factor

IF EXISTS ( SELECT
1
FROM
@Tmp_Info
WHERE
type = 1 )
BEGIN
SET @is_clustered = 1

END
ELSE
BEGIN
SET @is_clustered = 0

END

DECLARE @row_Count INT
SET @row_Count = (
SELECT
COUNT(*)
FROM
@tmp_Index_info
WHERE
type > 1
)
WHILE (@row_Count > 0)
BEGIN

SELECT
@index_id = index_id
,@Num_Key_cols = num_key_cols
,@Fixed_key_size = fixed_key_size
,@Num_var_key_cols = Num_var_key_cols
,@Max_var_key_size = Max_var_key_size
,@is_unique = is_unique
,@Num_Included_Cols = Num_Included_Col
,@Fill_Factor = fill_factor
FROM
@tmp_Index_info
WHERE
sno = @row_Count

IF (@is_clustered = 0)
BEGIN
SET @Num_Key_cols = @Num_Key_cols + 1
SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols
+ 1
END
ELSE
BEGIN
SELECT
@Num_Diff_cols = COUNT(column_id)
FROM
sys.index_columns x
,sys.indexes y
WHERE
column_id NOT IN (
SELECT
column_id
FROM
sys.index_columns a
,sys.indexes b
WHERE
a.index_id = b.index_id
AND type > 1
AND a.object_id = b.object_id
AND a.object_id = OBJECT_ID(@TableName)
AND a.index_id = @index_id)
AND x.object_id = y.object_id
AND y.type = 1
AND x.object_id = OBJECT_ID(@TableName)
AND x.index_id = y.index_id

SET @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols
+ @is_unique

SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols
+ @Num_Diff_cols + @is_unique

END

SELECT
@Num_Null_key_cols = ISNULL(COUNT(x.column_id), 0)
FROM
sys.index_columns x
,sys.columns y
WHERE
x.column_id = y.column_id
AND x.index_id = @index_id
AND y.is_nullable = 1
AND x.object_id = OBJECT_ID(@TableName)
AND x.object_id = y.object_id
DECLARE @index_name VARCHAR(100)

SELECT
@index_name = name
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(@TableName)
AND index_id = @index_id
SELECT
@Index_Space_Used = dbo.fn_getIndexSpace(@Num_Null_key_cols,
@Num_var_key_cols,
@Max_var_key_size,
@Fixed_key_size,
@Num_Rows)
SELECT
@Leaf_Level_Space = dbo.fn_GetLeafLevelIndexSpace(@Num_Leaf_Cols, @Num_var_key_cols, @Max_var_key_size, @Fixed_key_size, @Fill_Factor, @Num_Rows)

SET @Total_Index_space = @Total_Index_space + @Index_Space_Used
+ @Leaf_Level_Space

SET @row_Count = @row_count - 1
END

RETURN LTRIM(STR((@Total_Index_space)) / (1024))
END


GO


CREATE FUNCTION [dbo].[fn_GetTableSize]
(
@TableName VARCHAR(100)
)
RETURNS VARCHAR(25)
AS BEGIN
DECLARE @TableSize VARCHAR(25)
IF EXISTS ( SELECT
1
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(@TableName)
AND type = 1 )
BEGIN
SELECT
@TableSize = dbo.fn_ClusteredIndexSize(@TableName)
+ dbo.fn_GetNonClusteredIndexSize(@TableName)
END
ELSE
BEGIN
SELECT
@TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0)
+ ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName),
0))
END
SET @TableSize = LTRIM(STR(@TableSize)) + ' KB'
RETURN @TableSize
END




Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search