Script to get all Table sizes in SQL Server

Monitoring individual table’s growth is essential operation for DBA as well as application developer\support members. This will be helpful to take certain decision like to go for Archival process, table partitioning, backup and implementing high availability solution etc.,

Below script will automate this process. We just need to define threshold value and schedule it through SQL Agent or task scheduler. It also helps to pro-actively preventing unavoidable situations for Database and Applications.

CREATE PROCEDURE [USP_FETCH_ALL_TABLE_Size]

AS

DECLARE @TableName VARCHAR(100)

DECLARE CUR_Sysobjects CURSOR

FOR

SELECT

                        [name]

FROM

                        sysobjects

WHERE 

                        OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

DECLARE @Tab_Var AS TABLE

(

    TableName                                  VARCHAR(100),

    NumberOfRows            INT,

    ReservedSize                  VARCHAR(50),

    DataSize                                       VARCHAR(50),

    IndexSize                                     VARCHAR(50),

    UnusedSize                                 VARCHAR(50)

)

OPEN CUR_Sysobjects

--Get the first table name from the cursor

FETCH NEXT FROM CUR_Sysobjects 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  @Tab_Var

        EXEC sp_spaceused @TableName

    --Get the next table name

    FETCH NEXT FROM CUR_Sysobjects INTO @TableName

END

CLOSE CUR_Sysobjects

DEALLOCATE CUR_Sysobjects

--Select all records so we can use the reults

SELECT 

                        TableName AS TableName,

                        NumberOfRows AS NumberOfRows,

                        CAST ((CONVERT(NUMERIC, substring(dataSize,1, charindex('KB', dataSize)-1))/1024/1024) AS DECIMAL(6,2)) As 'DataSize IN GB'           

FROM

                        @Tab_Var

Order BY

                        NumberOfRows DESC

--EXEC [USP_FETCH_ALL_TABLE_Size]