Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Estimating the table size... Expand / Collapse
Author
Message
Posted Sunday, June 21, 2009 1:18 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
Comments posted to this topic are about the item Estimating the table size...

Regards,
Sqlfrenzy

Post #738998
Posted Thursday, July 02, 2009 7:41 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 75, Visits: 405
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
Post #746269
Posted Thursday, July 02, 2009 10:41 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
do you mean that it is not working....

Regards,
Sqlfrenzy

Post #746458
Posted Thursday, July 02, 2009 11:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 1,343, Visits: 373
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 --
Post #746504
Posted Thursday, July 02, 2009 12:33 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
well... I just scripted the function thru ssms copied them to the editor... I will try to remove the characters.....

Regards,
Sqlfrenzy

Post #746552
Posted Thursday, July 02, 2009 12:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:11 PM
Points: 242, Visits: 676
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
Post #746554
Posted Thursday, July 02, 2009 1:09 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
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

Post #746571
Posted Thursday, July 02, 2009 1:11 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
I have attached the updated script...once published it will be available for viewing....

Regards,
Sqlfrenzy

Post #746575
Posted Thursday, July 02, 2009 1:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:11 PM
Points: 242, Visits: 676
ah, yes.

pasted it in notepad and got to see it

quick replace fixed it


--
Thiago Dantas
@DantHimself
Post #746581
Posted Thursday, July 02, 2009 1:20 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 75, Visits: 405
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
Post #746583
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse