Scripts to find the Size of Table Objects

One of my friends asks me about the scripts related to finding the Size of the table objects, index size. He thought that his DB is growing unnaturally and he want to monitor each table (User define) objects size.

So in this article I am providing a stored procedure that display all the table size and related things in the current database.

You can supply the table name as the parameter to see the size of the table or just blank to see the entire user define table size of the current database. The stored procedure is ready to use.

/*

   To check the DB Size and DB Object Size Like Table Size /Index Size etc.

   Note : U must Specify the @P_TBLNAME as the DESIRED table name

   Version: 1.00

  

   Date: 30-March-2012

   by : joydeep Das

  

  

   EXEC up_RUN_SIZOMEETER

        @P_TBLNAME='MY_TABLE'

       

       

*/

IF EXISTS (SELECT *

           FROM   sysobjects WHERE type = 'P'

                  AND name = 'up_RUN_SIZOMEETER')

      BEGIN

            DROP  PROCEDURE  up_RUN_SIZOMEETER

      END

GO

CREATE Procedure [dbo].[up_RUN_SIZOMEETER]

      (

            @P_TBLNAME  VARCHAR(MAX) =    NULL   

      )

AS

    DECLARE  @v_TblName VARCHAR(MAX)

    DECLARE  @v_TRBL TABLE

                   (Tblname   VARCHAR(MAX),

                    TblRows   INT,

                    TblRese   VARCHAR(MAX),

                    Tbldata   VARCHAR(MAX),

                    TblIndx   VARCHAR(MAX),

                    TblUnused VARCHAR(MAX))

                   

BEGIN

        -- Cursor Declerations

        IF ISNULL(@P_TBLNAME,'')=''

           BEGIN

                        DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name LIKE @P_TBLNAME+'%'

               END

            ELSE

               BEGIN

                    DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name = @P_TBLNAME

               END                                   

        -- Cursor Open

        OPEN db_cursor

        -- Fatching the Cursor 

        FETCH NEXT FROM db_cursor

                   INTO @v_TblName

 

        WHILE @@FETCH_STATUS = 0  

                    BEGIN

                    INSERT INTO @v_TRBL  EXEC ('sp_spaceused '+ @v_TblName)

                  

                    FETCH NEXT FROM db_cursor

                               INTO @v_TblName

              END

                      

       CLOSE db_cursor  

         DEALLOCATE db_cursor

         

         SELECT Tblname   name,   

                    TblRows   [rows],  

                    TblRese   [reserved],

                    Tbldata   [data],

                    TblIndx   [Index Size],

                    TblUnused [Unused]

         FROM @v_TRBL

END

GO

To Execute the Stored procedure

EXEC up_RUN_SIZOMEETER

        @P_TBLNAME='MY_TABLE'

  

 OR

EXEC up_RUN_SIZOMEETER

        @P_TBLNAME=''

Hope you like that.

Posted by: MR. JOYDEEP DAS