What is INFORMATION_SCHEMA? What databases support it?

INFORMATION_SCHEMA is schema with a set of standard views/tables (depending on specific database engine) providing access to the database metadata and data dictionary - list tables, views, columns, stored procedures or functions in a database. This standard is part of SQL-92 and was implemented by some of the popular databases.

Standard views

SQL-92 specifies a set of standard tables/views that each database should support:

Some of the standard views:

  • TABLES - lists tables in a database
  • VIEWS - lists views in database
  • COLUMNS - lists table and view columns
  • TABLE_CONSTRAINTS - lists table constraints: unique, primary, foreign key and check constraints.
  • REFERENTIAL_CONSTRAINTS - lists foreign keys

Full list includes over 20 views.

Deviations from standard and issues

The standard was widely adopted by database vendors, yet no one impmenation was fully compliant with it. Vendors treated it more as a guidelines rather that fixed standard.

There are a number of deviations and issues with information_schema views in different databases.

Ommited views

Some views are simply ingored and not supported. Example of such case is VIEW_COLUMN_USAGE in MySQL.

Missing data

Quite often, views and columns are in place but data is missing or not reliable.

Added views and columns

Because databases have their specific elements vendors add their own columns. For instance, MySQL has a ROUTINES view that holds stored procedures and user defined functions.

Alternative catalogs

Because infortmation_schema stadard is limiting and doesn't reflect unique features of databases vendors created their own system catalogs (data dictionaries). SQL Server has sys schema, Oracle ALL_, USER_, and DBA_ views and PostgreSQL has pg_ views.

See the list of database system catalogs

What databases support INFORMATION_SCHEMA views

Below is a summary of information_schema support by various SQL databases. In Alternative catalog column you will find database specific catalogs, that in most cases are recommended over information_schema.

Database Information schema Alternative catalog
SQL Server Yes System Catalog Views (sys schema)
Azure SQL Database Yes System Catalog Views (sys schema)
Oracle database No Data Dictionary Views / Catalog Views
MySQL Yes No
PostgreSQL Yes System Catalogs (pg_)
MariaDB Yes No
IBM Db2 No Catalog views (SYSCAT schema)
Microsoft Access No Database obejct listing (MSysObjects)
Amazon Redshift Yes System Catalog Tables (pg_)
Snowflake Yes No
Teradata No Data Dictionary Views (DBC schema)
SAP/Sybase ASE No System Tables (sys)
SAP HANA No System Views (sys schema)
Vertica No System Tables (v_catalog schema)
IBM Informix Yes System Catalog Table ("informix".sys)
SQLite No System Table (sqlite_master)
Firebird No System Tables (names begin with RDB$)
Interbase No System Tables (names begin with RDB$)

Information_schema / system catalog queries

We are proud that we launched and commit to updating grand repository of queries to system catalogs of various popular databases.

Visit Database Query Toolbox - probably the biggest repository of queries (after StackOverflow) on the web.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.