Qualification of system tables in queries

I found an "undocumented" stored procedure that returns the size of the
tempdb called sp_tempdbspace - this stored procedure queries two different
tables to compute the allocated size of tempdb as well as figuring out how
much of that size is being used.
My question is about the queries used in this stored procedure - the query
that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but the
query that accesses sysindexes qualifies the table as "tempdb..sysindexes".
I am trying to figure out why these two queries use different qualifications
for these system tables - I suspect that there is a reason for this
difference other than cosmetic or stylistic reasons. I have tried to do
research through web searches, but have not really found anything that looks
to be conclusive.
Any answers or pointers in resolving this would be appreciated.My bet is that it's just a different style (maybe two different people
worked on the query, or maybe the same person at different times). Since
the stored procedure is always called with dbo either implicitly or
explicitly, it doesn't make a whole lot of difference. Especially if it is
just a cosmetic issue, in an undocumented procedure, I wouldn't worry about
it... I would worry more about relying on this stored procedure
(undocumented stored procedures can be changed, or dropped from the product
altogether, without any notice).
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
Perhaps they were written by two different programmers. Best practice is to
fully qualify the name. And **NEVER** rely on any script written by MS or
shipped with MS to be a good example of best practices.|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons.
AFAIK, the best practice is to always owner-qualify objects. This can
reduce stored procedure recompilations and avoid ambiguity.
Although there could be a special case for system object access, the more
likely reason for the omission of 'dbo' here is a simple oversight.
Hope this helps.
Dan Guzman
SQL Server MVP
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>