0

I am looking at a report for upgrade compatability on sql server and seeing the following error:

Table sysobjects has changed or does not longer exist after SQL Server 2005. Using it may cause errors. For more details, please see: Line X, Column Y.

We are moving to SQL server 2016 what would I need to replace sysobjects with.

The portion of the stored proc that is generating the above 110 compatibility message looks as follows:

SELECT name FROM sysobjects

otc
  • 694
  • 1
  • 9
  • 40
  • 1
    check if select * from master.sys.objects will work for you. I am not sure if it holds the same data. – PacoDePaco May 24 '17 at 10:42
  • 1
    What do you find if you search for `sysobjects`? For me, the top several links all go to versions of [this page](https://technet.microsoft.com/en-us/library/ms177596(v=sql.110).aspx): "This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views..." – Damien_The_Unbeliever May 24 '17 at 10:43
  • @PawełKucharski both SELECT name FROM sys.objects; and select * from master.sys.objects; produce the same results. would master.sys.objects be backward compatible? and why would it be better then the existing one? – otc May 24 '17 at 10:49
  • 1
    I thought you meant sysobjects (which I never heard of) not sys.objects. I just queried sys.objects on sql server 2016 so I can ensure you that it is there, so your procedures should work if you take a good look at the columns. Obviously if you are connected to the master database then master.sys.objects is the same as sys.objects. Try switching to the new way as suggested in the article though. – PacoDePaco May 24 '17 at 10:55
  • sry new to this, was just copying from the message from the compatability report. is there any other way to avoid using sys.objects that you know of? – otc May 24 '17 at 10:59
  • The list of undocumented system tables that are removed is provided in the below article. Remove references to undocumented system tables (https://go.microsoft.com/fwlink/?LinkID=708254) The "Corrective Action" provides the alternative replacements for some of the unsupported objects that can be used to modify your applications. – otc May 24 '17 at 11:00
  • 2
    One thing you need to understand: there is a master.sys.objects table and a master.sys.sysobjects view. The view is there to provide you with data that you used in SQL Server 2005. So "select * from master.sys.objects" and "select * from master.sys.sysobjects" do NOT retrieve the same dataset. – PacoDePaco May 24 '17 at 11:01
  • @PawełKucharski thank you after looking more into this makes sense that need to put sys. and point it to the view instead of hitting the object directly. – otc May 24 '17 at 13:10
  • @PawełKucharski will sys.sysobjects work on the sql server 2016 – otc May 24 '17 at 13:45
  • 1
    Yes, it exists in SQL Server 2016 as a view in the master database for backwards compability as stated in @sepupic 's answer. You should use the new methods instead in the future though. – PacoDePaco May 24 '17 at 19:12

1 Answers1

1

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Here you can find the list of System Compatibility Views

Besides, if you search in google for sysobject/other "old" system table, the first thing that will be written there is that the table is deprecated, use new_ system_view instead, this way you can find the new one

sepupic
  • 8,409
  • 1
  • 9
  • 20