What Is an Information Schema?

The information schema in a relational database is a separate table that documents information about the database itself. This background information is about many different aspects of the relational database; includes data about tables, rows, columns and records; and helps database designers check this information. Even though this is part of the relational database, the information schema acts as a separate database, though the schema does not generate as a separate database when placed on a server. While this schema is a standard used by database designers, it is commonly accessed by non-standard prompts. All information in this schema is read-only, so users cannot change or manipulate data from the schema.

Databases have so much information that most are typically impossible to read without some assistance. If database designers want to view metadata, or the information behind the database, then they can use the information schema. This displays most of the information within the database, such as the number of tables, the names of records or the titles of columns and rows. While the schema looks like a table, it is a view, meaning no files are linked to it.

This schema acts very differently from other forms in the database. The information schema, because of how it holds and displays information, acts like a separate database when users view the schema. At the same time, modification limitations mean it does not act like a traditional database. Unlike a traditional database, which can be generated when placed on a server, the information schema is not generated on a server. This means the information schema can only be accessed when viewed from a database program, not from the server.

Many database standards exist for security and stability, and the information schema is one of those standards. This means all relational databases, regardless of who built them, should contain this function. At the same time, non-standard commands are used to access the schema, which differentiates it from most other database standards. For example, the “show” and “describe” commands are used to view the schema, depending on the database model, even though they are not standard commands.

Changes and modifications are not allowed from the information schema, because it is set to read-only. This means users can only read data from the schema; they cannot perform any functions on the information. Regardless of users’ permission level, whether they are administrators or even database designers, there is no way of changing this, as of 2011.