What is INFORMATION_SCHEMA? What databases support it?

Piotr Kononow - Dataedo Team Piotr Kononow 2018-09-30

Table of Contents:


    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.

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept