Menu
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?
619
The INFORMATION_SCHEMA is a deprecated schema that I believe Microsoft keeps around because it's a notion of ANSI SQL (SQL-92) compliance. As the warning you quoted mentions, the INFORMATION_SCHEMA may be missing meta-data and information on some of the objects in your database.
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
information_schema is the database where the information about all the other databases is kept, for example, names of a database or a table, the data type of columns, access privileges, etc. It is a built-in virtual database with the sole purpose of providing information about the database system itself.
SYSOBJECTS contains a row for every object that has been created in the database, including stored procedures , views , and user tables (which are an important to distinguish from system tables .)
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.
144
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.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
