How to get table,database size in Sql Server

Introduction

In this article,i would share with you, how to get size of the tables in database in sql server.When you are move your database to production, then you have to check the database size once week or month. becuase host provider give the limited database capacity.

1.Get Database size.

EXEC sp_spaceused

 Result

In above figure,when we are excute the sp_spaceused without any parameters, it will take defult parameter as null and refurn the current database size and details about that database.

2.How to get a table size and other details.

EXEC sp_spaceused 'User_Details'

Result

In above figure, we have excute the same stored procedure, but this time we are specified the table name to get the detail.it has been return how many bytes used and how is allocated as well.even you able to see how many rows has in current state.

3.How to get all table size and other details

In this time we have to use our custom procedure to do this, becuase there is no inbuilt proc to get this details in sql server.how we can do this, let create a procedure and get all tables name from the database and fetch into cursor.

CREATE PROCEDURE spCodeGainSizeUtil
AS
DECLARE @TableName VARCHAR(100)    
DECLARE dataCursor CURSOR
		FOR 
		select [name]
		from dbo.sysobjects 
		where  OBJECTPROPERTY(id, N'IsUserTable') = 1
		FOR READ ONLY


CREATE TABLE #SizeTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN dataCursor

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

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
	BEGIN
	    
		INSERT  #SizeTable
			EXEC sp_spaceused @TableName	    
		FETCH NEXT FROM dataCursor INTO @TableName
	END
CLOSE dataCursor
DEALLOCATE dataCursor
SELECT * FROM #SizeTable
DROP TABLE #SizeTable

GO

In above procedure,i have compose the above the stored procedure using simple SQL.

Result

In above result, you able to see the all details in when you are execute the in sql server.

Sample Script

Download source files -1 kb