How to get table,database size in Sql Server

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.

EXEC sp_spaceused

In above Text,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

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
Enjoy in .net World!!!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s