HOWTO: Find out the Sizes of All the User Tables in a Database in SQL

Do you need to know the sizes for all the user tables in your database? I found this script by Mitchel Sellers, an SQL Server MVP,  to be very handy. I've modified it slightly, but you can get the original on his blog.

Thank you Mitchel for allowing me to post this here.

Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Mitchel Sellers - mitchel.sellers@gmail.com
-- Create date: 2007/06/27
-- Description: Obtains spaced used data for
--                            ALL user tables in the database.
--                            This does not update the space
--                            usage information - implicity
--                            running DBCC UPDATEUSAGE on the
--                            database - as recalculation has
--                            the potential to be VERY costly
--                            if it is run on ALL tables at
--                            once inside a database.
--
-- Modifications:
-- Author:            A. Jackson
-- Date:                2010/03/10
-- Description: Renamed the stored procedure
--                            from
--                            GetAllTableSizes to
--                            dba_GetAllTableSizes to fit my
--                            DBA sp naming convention.
--                        : Changed the select to order the
--                            result by TableName with the
--                            option to order the results by
--                            descending ReservedSizeKB.
--                            In order to do this I had to
--                            strip out the " KB" out of the
--                            size strings and alter the
--                            ReservedSizeKB column definition
--                            after stripping out the " KB".
--                        : Neatened up the SQL.
--                        : Corrected a spelling error in
--                            the comments:
--                            ("reults" -> "results")
-- Author:            A. Jackson
-- Date:                2010/03/24
-- Description: Changed the type of
--                            @OrderByReservedSize from
--                            VARCHAR(5) to BIT.
-- =============================================

CREATE PROCEDURE [dbo].[dba_GetAllTableSizes]
        @OrderByReservedSize BIT = 0
AS  
BEGIN
        --For storing values in the cursor
        DECLARE @TableName VARCHAR(100)
        --Cursor to get the name of all user tables from the sysobjects listing
        DECLARE tableCursor CURSOR FAST_FORWARD READ_ONLY FOR
                SELECT
                        [name]
                FROM
                        dbo.sysobjects
                WHERE
                        OBJECTPROPERTY(id, N'IsUserTable') = 1

        --A procedure level temp table to store the results
        CREATE TABLE #TempTable
                (
                 TableName VARCHAR(100),
                 NumberOfRows VARCHAR(100),
                 ReservedSizeKB VARCHAR(50),
                 DataSizeKB VARCHAR(50),
                 IndexSizeKB VARCHAR(50),
                 UnusedSizeKB VARCHAR(50)
                )

        --Open the cursor
        OPEN tableCursor

        --Get the first table name from the cursor
        FETCH NEXT FROM tableCursor INTO @TableName

        --Loop until the cursor was not able to fetch
        WHILE (@@Fetch_Status >= 0)
        BEGIN
                --Dump the results of the sp_spaceused query to the temp table
                INSERT #TempTable EXEC sp_spaceused @TableName

                --Get the next table name
                FETCH NEXT FROM tableCursor INTO @TableName
        END

        --Get rid of the cursor
        CLOSE tableCursor
        DEALLOCATE tableCursor

        --Strip out the " KB" out of the size strings so that they can be ordered.
        UPDATE
                [#TempTable]
        SET  
                [#TempTable].[ReservedSizeKB] = LEFT([#TempTable].[ReservedSizeKB], LEN([#TempTable].[ReservedSizeKB]) - 3),
                [#TempTable].[DataSizeKB] = LEFT([#TempTable].[DataSizeKB], LEN([#TempTable].[DataSizeKB]) - 3),
                [#TempTable].[IndexSizeKB] = LEFT([#TempTable].[IndexSizeKB], LEN([#TempTable].[IndexSizeKB]) - 3),
                [#TempTable].[UnusedSizeKB] = LEFT([#TempTable].[UnusedSizeKB], LEN([#TempTable].[UnusedSizeKB]) - 3)
        --Alter the temp table so that the results can be ordered.
        ALTER TABLE [#TempTable] ALTER COLUMN [ReservedSizeKB] BIGINT
        
        --Select all records so we can use the results.
        --Optionally ORDER BY ReservedSizeKB
        IF (@OrderByReservedSize = 1)
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[ReservedSizeKB] DESC
        END
        ELSE
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[TableName]
        END
        
        --Final cleanup!
        DROP TABLE #TempTable
END
GO