Find space utilization by SQL Server objects (sp_spaceused)

You can use sp_spaceused to find out the space utilization by a database object (like table, indexed view etc or even for a database)

Use below syntax to use sp_spaceused:

sp_spaceused [[ @objname= ] 'objname' ]

[,[ @updateusage= ] 'updateusage' ]

Example:

Find the details of space used by a database:

USE [Databasename];

GO

EXEC sp_spaceused

GO

Example:

Find the details of space used by a table of a database:

USE [Databasename];

GO

EXEC sp_spaceused N'[Tablename]'; -- You can put any qualifying object name here instead of Tablename

GO

Please note Microsoft does not take guarantee that you will always find the correct space utilization report for a database object by using only sp_spaceused. Hence you can explicitly instruct SQL Server to ensure that it return correct (latest) values by using @updateusage as below:

USE [Databasename];

GO

EXEC sp_spaceused @updateusage = N'TRUE';

GO

For more, please refer to http://msdn.microsoft.com/en-us/library/ms188776.aspx