Take a look two SP inside the sys Procedure in SQL Server 2008 (included also in the SQL Server 2005), and they are the documented one which is “sp_SpaceUsed” and undocumented one “sp_MSTableSpace“.
Yes, it is documented store procedure inside the SQL Server 2008 (incl, SQL Server 2005), and the way how to execute it is:
EXEC sp_spaceused ‘TableName’
For very few seconds, for the table that you choose, you will have info about:
How many rows are in the table,
How much space are reserved for that table,
How many KB data are allocated,
What is the SUM of Indexs Size,
What about your unused space.
EXEC sp_MSTableSpace ‘TableName’
The second one is the undocumented store procedure that shows less results than SP_SPACEUSED but it is usefull for quick info about a table.
The info that we can have from this Sys SP is:
Number of Rows,
Data Space Used,
Index Space Used.
As you can see, two SYS SPs tell us a little bit same info about a table, exactly the Rows Number and Data Space Used. Anyway two SPs are very quick and very usefull.