Friday 30 March 2012

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

No comments:

Post a Comment