aiDeeva.com

Tag: sys.SP_SpaceUsed

  • Display the size of all tables in Sql Server 2005

    You can use sys.SP_SpaceUsed stored procedure to get the size of all the tables.

    Below query will calulate the space of all the tables.
    –create temp table to store the result
    CREATE TABLE #temptable
      (
         tablename    VARCHAR(100),
         numberofrows VARCHAR(100),
         reservedsize VARCHAR(50),
         datasize     VARCHAR(50),
         indexsize    VARCHAR(50),
         unusedsize   VARCHAR(50)
      )

    –Inserting into temp table

    INSERT INTO #temptable
                (tablename,
                 numberofrows,
                 reservedsize,
                 datasize,
                 indexsize,
                 unusedsize)

    EXEC Sp_msforeachtable @command1=“EXEC sp_spaceused ‘?’”

    SELECT *
    FROM   #temptable

    — drop the temporary table
    DROP TABLE #temptable 

     

    Size of All the tables