x

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?

more ▼

asked May 04, 2010 at 05:17 PM in Default

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

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...
May 04, 2010 at 05:23 PM Matt Whitfield ♦♦
LOL that is worth a +1
May 04, 2010 at 08:41 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.

more ▼

answered May 04, 2010 at 05:49 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Those are some good points. That is one of the primary differences. However, Clifford makes a recommendation to use sysindexes when sp_MStablespace relies on sysindexes as well. Another problem is that sysindexes is not a table, it is a view.
May 04, 2010 at 08:40 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407
x27
x11

asked: May 04, 2010 at 05:17 PM

Seen: 847 times

Last Updated: May 04, 2010 at 05:17 PM