24

In SQL Server:

What is the difference between INFORMATION_SCHEMA and sysobjects? Does one provide more information than the other or are they used for different things usually?

Is sysobjects the same as sys.objects? If not, what is the difference between these?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dismissile
  • 32,564
  • 38
  • 174
  • 263
  • 5
    `sysobjects` is deprecated as of SQL Server 2005 - use the catalog views in the `sys` schema instead: `sys.tables`, `sys.indexes` and so forth – marc_s Dec 07 '10 at 22:16

2 Answers2

35

The INFORMATION_SCHEMA is part of the SQL-92 standard, so it's not likely to change nearly as often as sysobjects.

The views provide an internal, system table-independent view of the SQL Server metadata. They work correctly even if significant changes have been made to the underlying system tables.

You are always much better off querying INFORMATION_SCHEMA, because it hides the implementation details of the objects in sysobjects.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
7

INFORMATION_SCHEMA is an ANSI standard, somewhat extented in its SQL Server implementation. sysobjects is specific to SQL Server. Old versions of SQL Server did not support it.

So INFORMATION_SCHEMA is more portable (works on other database) and somewhat easier to use than sysobjects. If it has the information you need, I would go for INFORMATION_SCHEMA.

sys.objects sysobjects is only there for SQL 2000 portability.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Simon Mourier
  • 132,049
  • 21
  • 248
  • 298
  • 3
    Actually: `sys.objects` is the new SQL Server 2005 version of the catalog view; `sysobjects` (no dots) is the old, backwards-compatible SQL Server 2000 catalog table – marc_s Dec 07 '10 at 22:17
  • I meant sys.sysobjects (unfortunately, I copied the original text...) as stated here: http://msdn.microsoft.com/en-us/library/ms177596.aspx – Simon Mourier Dec 07 '10 at 23:09