SP_SPACEUSED–Determine size (Space) - SQL Server TIP #1

 

To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED.

Syntax is simple enough as shown below

1) If we want to determine size of the database then we have to write below statement and execute

Use DatabaseName

GO

SP_SPACEUSED

GO

2) Now to determine size of particular table  we need to write following syntax

GO

SP_SPACEUSED TABLENAME

GO

3) Now to determine size of each table in a database we need to write following syntax

GO

SP_MSFOREACHTABLE ‘SP_SPACEUSED [1]’

GO

4) When we are running sp_spaceused it may possible the size is not updated so to make sure size is updated we need to write following command

GO

SP_SPACEUSED @updateUsage =N’True’

GO

In the output of each statements we are seeing reserved, data,Index_Size & unused columns.

so

1) reserved column shows total space reserved by object.

2) data column shows total space used by the data.

3) index_Size shows total space used by index in object.

4)unused shows total space reserved by object but not yet used.

For more information you can visit following link

http://msdn.microsoft.com/en-us/library/ms188776.aspx

Enjoy.