Wednesday, 20 November 2013

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]