Information Schema

If you have question regarding this information or if I have something wrong please email me at vbsqliteNOSPAM@NOSPAMag-software.com (remove the NOSPAM's)

The information schema consists of a set of views that contain information about the objects defined in the current database. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable.

SQLite_Master is the base table where schema information is stored, these views query this table

SQLite doesn't support the idea of different owners of objects so the naming convention for the INFORMATION_SCHEMA views are
INFORMATION_SCHEMA_OBJECTNAME

Information_Schema_TABLES
The view TABLES contains all tables and views defined in the current database

CREATE VIEW INFORMATION_SCHEMA_TABLES AS
    SELECT * FROM (
        SELECT 'main'     AS TABLE_CATALOG,
               'sqlite'   AS TABLE_SCHEMA,
               tbl_name   AS TABLE_NAME,
               CASE WHEN type = 'table' THEN 'BASE TABLE'
                    WHEN type = 'view'  THEN 'VIEW'
               END        AS TABLE_TYPE,
               sql        AS TABLE_SOURCE
        FROM   sqlite_master
        WHERE  type IN ('table', 'view')
               AND tbl_name NOT LIKE 'INFORMATION_SCHEMA_%'
        UNION
        SELECT 'main'     AS TABLE_CATALOG,
               'sqlite'   AS TABLE_SCHEMA,
               tbl_name   AS TABLE_NAME,
               CASE WHEN type = 'table' THEN 'TEMPORARY TABLE'
                    WHEN type = 'view'  THEN 'TEMPORARY VIEW'
               END        AS TABLE_TYPE,
               sql        AS TABLE_SOURCE
        FROM   sqlite_temp_master
        WHERE  type IN ('table', 'view')
               AND tbl_name NOT LIKE 'INFORMATION_SCHEMA_%'
    ) ORDER BY TABLE_TYPE, TABLE_NAME;

Columns Description

Name	Description
table_catalog	Name of the database that contains the table (always the main)
table_schema	Name of the schema that contains the table
table_name	Name of the table or View
table_type	Type of the table: BASE TABLE for a persistent base table (the normal table type), BASE VIEW for a view, or TEMPORARY TABLE for a temporary table and TEMPORARY VIEW for a temporary view.
table_source	The SQL script with which the object was created.

(Compare PostgreSQL's <a href='http://www.postgresql.org/docs/current/static/information-schema.html'>information schema documentation.)