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 tablesviewscolumns, 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

  1. COLUMNS
  2. COLUMN_PRIVILAGES
  3. COLUMN_DOMAIN_USAGE
  4. CONSTRAINT_COLUMN_USAGE
  5. TABLES
  6. TABLE_CONSTRAINTS
  7. TABLE_PRIVILAGES
  8. CONSTRAINT_TABLE_USAGE
  9. VIEWS
  10. VIEW_COLUMN_USAGE
  11. VIEW_PRIVILAGES
  12. DOMAINS
  13. DOMAIN_CONSTRAINTS
  14. ROUTINES
  15. ROUTINE_COLUMNS
  16. PARAMETER
  17. REFERENTIAL_CONSTRAINTS
  18. KEY_COLUMN_USAGE
  19. SCHEMATA
  20. SEQUENCES
  21. CHECK_CONSTRAINTS

More on Information_Schema

Advertisements