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

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