Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joeller
Starting Member
15 Posts |
 Posted - 2012-05-14 : 10:57:29
|
My update guide to SQL Server 2005 reported quote:
In earlier versions of SQL Server, system objects are owned by dbo and reside in the master database. In SQL Server 2005, system objects are owned by sys and logically appear in every database. Statements that query system tables and have search criteria that specify the user dbo will fail.
So why does the query "Select * from dbo.sysobjects" still work, and in fact in SQL Server 2008 the sysobjects shows up colored green as a system view. Yet there is no view called "dbo.sysobjects", but there is one called "sys.sysobjects". Since SQL Server 2005 came out you supposedly had to refer to objects by the correct schema if they did not exist in the dbo schema to get a result. Yet for several system compatibility views, it doesn't seem to matter if you enter either dbo or sys. yet if I enter erj.sysobjects, this does not work.Does any one know why this continues to work?E.R. Joell MCDBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-14 : 11:39:33
|
| I can't say for sure, but my guess is that Microsoft simply maintained it for backwards compatibility.Best thing to do is remove all references to sysobjects and use sys.objects exclusively. At some point sysobjects will stop working entirely. |
 |
|
|
joeller
Starting Member
15 Posts |
Posted - 2012-05-14 : 14:29:09
|
| Bear in mind I am not talking about the sys.sysobjects view which they broadcast the fact that this was created for backwards compatibility. I am talking about something that appears, from its usage, to exist in the dbo schema (invisibly if so), that either creates an identical view as sys.sysobjects or calls sys.sysbjects. This was after all the big commotion about why using the proper schema in your queries was really important, and after Microsoft has stated that unless a table or a view exists in either the dbo schema or your default schema, then in order for a query against it to work, that it must be qualified with the schema name. So why do we either not have to qualify it or are able to qualify it incorrectly.The reason I am concerned is that I was getting ready to rip some developers a new one for using an obsolete unworkable formulation (Select * from dbo.sysobjects instead of Select * from sys.sysobjects), when lo and behold it actually worked. While we are moving our developers slowly towards using the new style system views, we are weaning them from the old system tables by qualifting the calls to the new systems views, including those retained for backwards compatibility. So I was highly irritated with them for continuing to use dbo.sysobjects but then puzzled when it actually worked.E.R. Joell MCDBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-14 : 17:23:30
|
| As I said, it's probably for backwards compatibility even though the documentation says it shouldn't work. Regardless of why it works, the point is that sysobjects, sysindexes, and all the pre-2005 system tables are marked as deprecated and will be dropped in a future release. Instead of concerning yourself with why the obsolete items still work, focus on changing your code to use the new system catalogs like sys.objects, etc. |
|
|
|
joeller
Starting Member
15 Posts |
Posted - 2012-05-15 : 08:51:29
|
| If it still works and is supposed to work the government will not authorize the money to fix it. The only way the money to make these changes would be forthcoming is if the migration from SS 2005 to SS 2008 R2 would break it. So far as I am seeing it is not breaking it, even though the documentation says that it should. If Microsoft is not going to make this invalid in SS 2008 R2 right now (then that will have to stay as it is for about 2 or three years until the goverment is ready to migrate to SS 2012. And if it works in there than the change won't be made even then.E.R. Joell MCDBA |
|
|
|
joeller
Starting Member
15 Posts |
Posted - 2012-05-15 : 09:05:15
|
| To clarify the previous. I can get the other contractors to develop using the correct system views when carrying out new work. But I can not persuade the government authorize expenditure for them to re-work old code using old SS 2000 code that actually works. (I can't even get them to authorize money for soap and paper towels in the bathrooms. Cutbacks.)E.R. Joell MCDBA |
|
|
|
|
|
|
|
|
Why does dbo.sysobjects (Not sys.sysobjects) work?