Behind every well-structured and organised SQL database lies a treasure trove of information called metadata. Metadata is data that provides information about other data. In relational database management systems, this metadata can typically include information like the names of databases, tables, columns, the data type of a column, access privileges, and much more.
The SQL standard provides a collection of tables/views that allow us to view metadata about the objects in the database. The SQL standard refers to this as the information schema and calls it INFORMATION_SCHEMA.
What is INFORMATION_SCHEMA?
The information schema (INFORMATION_SCHEMA) is an ANSI/ISO-standard schema in relational database management systems (RDBMSs) that provides a comprehensive view of the metadata associated with each database. It acts as a catalog that houses metadata about tables, views, stored procedures, columns, constraints, and other database objects.
The information schema consists of a collection of read-only views that provide information about all of the tables, views, columns, procedures, and more in a database. Whenever we create a new object in the database, we can query the information schema to get information about that object.
INFORMATION_SCHEMA is an integral part of many popular RDBMSs, such as MySQL, PostgreSQL, MariaDB, and SQL Server, offering a consistent way to access and retrieve valuable information about the database structure.
In the SQL standard, the information schema is defined as a collection of tables. However, many RDBMSs implement the information schema as a collection of views. These views contain columns that correspond to those set out in the standard. Having the information schema implemented as a collection of views provides an internal, system table-independent view of the metadata. Information schema views enable applications to work correctly even if significant changes have been made to the underlying system tables.
Examples of INFORMATION_SCHEMA Tables
Here are examples of some of the tables in the information schema:
| Table | Description |
|---|---|
TABLE | Provides information about tables in the databases. |
COLUMNS | Provides information about columns in the tables. |
ROUTINES | Provides information about stored routines (stored procedures and stored functions). |
VIEWS | Provides information about views in the databases. |
CHECK_CONSTRAINTS | Provides information about CHECK constraints. |
That’s just a small sample of tables in the information schema. The full list depends on the DBMS being used. Check the documentation for your DBMS for a full list.
How to Use the Information Schema
As database developers or administrators, INFORMATION_SCHEMA provides us with a standard way of viewing important information about the databases on the server. For example, we can use INFORMATION_SCHEMA to get a list of tables in a database, along with their columns and their data types. And we can use the same code against other database systems (as long as they support the information schema), without having to rewrite anything.
Example
Here’s an example of a query that queries the COLUMNS view in the information schema:
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH AS "MAX_LENGTH",
CHARACTER_OCTET_LENGTH AS "OCTET_LENGTH",
NUMERIC_SCALE AS "SCALE",
NUMERIC_PRECISION AS "PRECISION"
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Products'
AND TABLE_SCHEMA = 'krankykranes';Result:
+------------------+-----------+------------+--------------+-------+-----------+ | COLUMN_NAME | DATA_TYPE | MAX_LENGTH | OCTET_LENGTH | SCALE | PRECISION | +------------------+-----------+------------+--------------+-------+-----------+ | ProductId | int | NULL | NULL | 0 | 10 | | ProductName | varchar | 255 | 1020 | NULL | NULL | | ProductPrice | decimal | NULL | NULL | 2 | 8 | | RecommendedPrice | decimal | NULL | NULL | 2 | 8 | +------------------+-----------+------------+--------------+-------+-----------+
In this example I returned information about the columns in a table called Products in the krankykranes database. I was able to get this information from the INFORMATION_SCHEMA.COLUMNS table/view.
Note that I qualified the view with INFORMATION_SCHEMA so that my DBMS knew which schema to use. More on this next.
As with any SQL query, we can include the columns we need and exclude those we don’t. We can also use SQL joins to include information from other tables or views in the system (including other INFORMATION_SCHEMA tables/views).
One benefit of INFORMATION_SCHEMA is that we can use the same query against any other DBMS that supports INFORMATION_SCHEMA without having to change anything. If both DBMSs implement INFORMATION_SCHEMA as per the SQL standard, then we shouldn’t expect anything to break when we use the same query across different systems.
That said, many DBMSs implement their own proprietary extensions to the standard, and so we need to be mindful of this. Also, if a query joins other non-standard tables, then there’s always a possibility of it breaking when we try to port it to another DBMS.
Using the INFORMATION_SCHEMA Qualifier
Typically when we access the information schema tables/views, we need to qualify the table/view name with INFORMATION_SCHEMA. So if we’re accessing the TABLES view, we need to reference it as INFORMATION_SCHEMA.TABLES.
The above may depend on the DBMS though. For example, MySQL has an INFORMATION_SCHEMA database that we can set as the default database. So if we run USE INFORMATION_SCHEMA, we can then query any of the information schema views without qualifying them with the schema name.
Uppercase or Lowercase?
You may notice that many references to the information schema are in uppercase (INFORMATION_SCHEMA). However, this is not required. We can also access the information schema using lowercase.
So the following two examples both do the same thing:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM information_schema.tables;Both of those statements select all data from the same table/view in the information schema.
It’s the same as SQL itself. SQL commands and statements can be either uppercase or lowercase. It makes no difference to the result.
Alternatives to the Information Schema
Many RDBMSs implement their own proprietary data dictionary in addition to the information schema. Other RDBMSs don’t support the information schema at all, and implement their own proprietary data dictionary.
Therefore, we often have a choice of options when it comes to returning metadata from our database.
SQL Server for example, has a sys.columns view that returns similar information to the INFORMATION_SCHEMA.COLUMNS view. SQL Server also has a sp_help stored procedure that allows us to get lots of metadata about database objects, including tables and columns.
MySQL includes a bunch of SHOW statements (for example SHOW COLUMNS and SHOW TABLES) that return information about databases, tables, columns, or status information about the server.
Other DBMSs may have their own implementations, but if they support the information schema then this provides us with a standard method of getting metadata about the database.