I often got stuck in the jargon of technical words and how are they related. For those who are struggling like me to comprehend the technical buzz of words, this post illustrates the basic difference between the INFORMATION_SCHEMA, sys, sys.objects, dbo etc of SQL SERVER.
Foremost important point to remember is that– sys, INFORMATION_SCHEMA, dbo are the schemas in the database. These schemas will be available in the every database created on the SQL server. Views created inside these schemas are prefixed with the schema name as we can see the views in INFORMATION_SCHEMA are INFORMATION_SCHEMA.CHECK_CONSTRAINTS, INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE .
sys.objects is a view in sys schema which contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.
In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database.
An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables.
The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA. The information schema views are defined in a special schema named INFORMATION_SCHEMA. Each information schema view contains metadata for all data objects stored in that particular database.
An easy way to remember the INFORMATION_SCHEMA Views is to think from