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 convension 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_%'
) BT 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. |