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.
