Retrieve the list of tables, indices and the associated SQL statements

SQLite maintains the list of tables and indexes in a special system generated table called sqlite_master. The python example uses sqlite3 to query the sqlite_master and lists the tables and indexes.

SQLite Updated 26 Apr 2018 2 min read

Overview

  • Every SQLite database has a special table named sqlite_master, which is a system created table.
  • The sqlite_master table of a database contains the list of tables, indices and other details specific to that database.
  • The type column of the sqlite_master table denotes what type of database object the record is about. The type column will hold the value "table" for a database table and the value "index" for a database index.
  • The sql column of the sqlite_master table contains the SQL statement with which a table or index has been created.

Getting the list of tables and indexes present in a SQLite database using Python

  • The sqlite3 is the python database client for SQLite database.
  • A database connection to an SQLite database can be created by calling the connect() method of the sqlite3 module.
  • Through the connection object a sqlite3.Cursor object can be obtained.
  • A SQL statement like a SELECT statement can be executed on the SQLite server by calling the execute() method of the cursor object and passing the SQL statement as parameter.
  • The Python example below queries the sqlite_master table and prints the list of tables and indices present in the SQLite main database loaded through the file info.dbs.

Example

python
# Import the python module - sqlite3

import sqlite3

# Create database connection to a main database which is stored in a file

connectionObject    = sqlite3.connect("info.dbs")

# Obtain a cursor object

cursorObject        = connectionObject.cursor()

# Print the tables and indices present in the SQLite main database

cursorObject.execute("select * from SQLite_master")

tables = cursorObject.fetchall()

print("Listing tables and indices from main database:")

for table in tables:

        print("Type of database object: %s"%(table[0]))

        print("Name of the database object: %s"%(table[1]))

        print("Table Name: %s"%(table[2]))

        print("Root page: %s"%(table[3]))

        print("SQL statement: %s"%(table[4]))

connectionObject.close()

Output

Output
Listing tables and indices from main database:

Type of database object: table

Name of the database object: tokens

Table Name: tokens

Root page: 2

SQL statement: CREATE TABLE tokens(id int, value varchar(12), weight int)

Type of database object: table

Name of the database object: routes

Table Name: routes

Root page: 3

SQL statement: CREATE TABLE routes(id int, start int, stop int,hops int )

Type of database object: index

Name of the database object: idx_index

Table Name: tokens

Root page: 4

SQL statement: CREATE INDEX idx_index on tokens(value)