What are some of the differences between sp_MSTablespace and sp_spaceused?
Both are shipped from Microsoft.
Why do these procs return different values from time to time?
asked May 04, 2010 at 05:17 PM in Default
Looks like the biggest difference between the 2 is that the former is undocumented while the latter is documented and recommended by MSFT. Here is the paste from the old post by Clifford Dibbie (PM, SQL Server):
sp_MStablespace is an undocumented API, and we make no guarantees of forward compatibility in Yukon. Several undocumented tables and stored procedures are gone in Yukon (e.g., sysxlogins, sysproperties, sp_MSscript_where_clause, etc). The bottom line is this: If there is an index entry in the BOL and a BOL reference page dedicated to the API, then it is documented and you can use it with good confidence it will continue to work when you upgrade to Yukon. But if you can't find sp_MStablespace in the index or by searching the BOL, and there is no reference page describing it, then you are taking a big risk that your code won't upgrade.
Instead, I recommend you use the sysindexes system table and the sp_spaceused. All of the documented columns in the sysindex table will still work in Yukon for non-partitioned tables, so your code will upgrade.
For system tables, you should be able to upgrade as long as you avoid columns whose description says 'Reserved' or 'Internal Only'. The Yukon BOL talks about the upgrade issues of system tables in general, and sysindexes in particular, in greater depth.
The link to the post is here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/73c0ce76-4ac7-43d6-9b42-fb9d210e423c
sp_helptext shows that the former mostly gathers data from dbo.sysindexes while the latter involves dbo.sysfiles, sys.dm_db_partition_stats, sys.internal_tables and sys.allocation_units.
answered May 04, 2010 at 05:49 PM