question

CirqueDeSQLeil avatar image
CirqueDeSQLeil asked

Difference between MS Procs

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?

stored-proceduresseeder-questionfor-fun
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's because of the elves right? I've always wanted to know why those elves don't tell the same story from one day to the next...
1 Like 1 ·
LOL that is worth a +1
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.