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
2) Now to determine size of particular table we need to write following syntax
3) Now to determine size of each table in a database we need to write following syntax
SP_MSFOREACHTABLE ‘SP_SPACEUSED ’
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
SP_SPACEUSED @updateUsage =N’True’
In the output of each statements we are seeing reserved, data,Index_Size & unused columns.
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