How does I list all tables/indices contained in an SQLite database

How does I list all tables/indices contained in an SQLite database

If you is running the sqlite3 command-line Access program you can type ". Tables" To get a list of all T Ables. Or you can type '. Schema' to see the complete database schema including all tables and indices. Either of these commands can be followed by a like pattern that would restrict the tables that is displayed.

From within a C + + program (or a script using Tcl/ruby/perl/python bindings) You can get access to table and index names By doing a select on a special table named "sqlite_master". Every SQLite database has an Sqlite_master table, defines the schema for the database. The Sqlite_master table looks like this:

CREATE TABLE sqlite_master (  type text,  name text,  tbl_name text,  rootpage INTEGER,  sql text);

For tables, the type field would always be ' table ' and the name field would be the name of the TA ble. So-get a list of all tables in the database, use the following SELECT command:

SELECT name from Sqlite_masterwhere type= ' table ' ORDER by name;

For indices, type was equal to ' index ', name was the name of the the index and tbl_name is t He name of the table to which the index belongs. For both tables and indices, the SQL field is the text of the original create TABLE or create INDEX statement tha T created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the SQL field is NULL .

The Sqlite_master table is read-only. You cannot the change of this table using UPDATE, INSERT, or DELETE. The table is automatically updated by CREATE table, create index, drop table, and drop INDEX commands.

Temporary tables don't appear in the Sqlite_master table. Temporary tables and their indices and triggers occur in another special table named Sqlite_temp_master. Sqlite_temp_master works just like Sqlite_master except that it's only visible to the application that created the Tempor ary tables. To get a list of all tables, both permanent and temporary, one can use a command similar to the following:

Select name from    (SELECT * from Sqlite_master UNION all    SELECT * from Sqlite_temp_master) WHERE type= ' table ' ORDER B Y Name

How does I list all tables/indices contained in an SQLite database