Displaying Size of Sql Server database tables

We are in the process of refining some of our data types.  As part of this, we needed to know the space usage and row count of each table in the database before we begin.


We were able to use the system stored procedure: sp_spaceUsed.

The sp_spaceUsed stored procedure can be passed an argument:

        sp_spaceUsed 'myTable'

Which will then return the stats for that table: including the number of rows, table size and index size.

Using this stored procedure, along with the undocumented sp_MSforEachTable we can loop through all the tables in the database and dump the results into a temp table.


See the code below:



-- Create Table to hold all tables and their values
CREATE TABLE #TableUsage
(
      Name      nvarchar(128)
    , rows      int
    , reserved  varchar(50)
    , data      varchar(50)
    ,index_size varchar(50)
    ,unused     varchar(50)
)   

-- Use the undocumented stored procedure
    -- sp_MSforEachTable  with the sp_spaceUsed procedure
    INSERT INTO #TableUsage
    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"


-- Now view the result set
Select * FROM #tableUsage