SQLite Schema Information Metadata

Question

I need to get column names and their tables in a SQLite database. What I need is a resultset with 2 columns: table_name | column_name.

In MySQL, I'm able to get this information with a SQL query on database INFORMATION_SCHEMA. However the SQLite offers table sqlite_master:

sqlite> create table students (id INTEGER, name TEXT);
sqlite> select * from sqlite_master;
  table|students|students|2|CREATE TABLE students (id INTEGER, name TEXT)

which results a DDL construction query (CREATE TABLE) which is not helpful for me and I need to parse this to get relevant information.

I need to get list of tables and join them with columns or just get columns along with table name column. So PRAGMA table_info(TABLENAME) is not working for me since I don't have table name. I want to get all column metadata in the database.

Is there a better way to get that information as a result set by querying database?

Answer 1

You've basically named the solution in your question.

To get a list of tables (and views), query sqlite_master as in

SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

(see the SQLite FAQ)

To get information about the columns in a specific table, use PRAGMA table_info(table-name); as explained in the SQLite PRAGMA documentation.

I don't know of any way to get tablename|columnname returned as the result of a single query. I don't believe SQLite supports this. Your best bet is probably to use the two methods together to return the information you're looking for - first get the list of tables using sqlite_master, then loop through them to get their columns using PRAGMA table_info().

Answer 2

Recent versions of SQLite allow you to select against PRAGMA results now, which makes this easy:

SELECT 
  m.name as table_name, 
  p.name as column_name
FROM 
  sqlite_master AS m
JOIN 
  pragma_table_info(m.name) AS p
WHERE
  m.type = 'table' 
ORDER BY 
  m.name, 
  p.cid

where p.cid holds the column order of the CREATE TABLE statement, zero-indexed.

David Garoutte answered this here, but this SQL should execute faster, and columns are ordered by the schema, not alphabetically.

Note that table_info also contains

• type (the datatype, like integer or text),
• notnull (1 if the column has a NOT NULL constraint)
• dflt_value (NULL if no default value)
• pk (0 if the column is not the table's primary key, 1 if it's the first primary key, and for composite primary keyed tables, 2 or larger values, based on key ordinal)

RTFM: https://www.sqlite.org/pragma.html#pragma_table_info

Answer 3

There are ".tables" and ".schema [table_name]" commands which give kind of a separated version to the result you get from "select * from sqlite_master;"

There is also "pragma table_info([table_name]);" command to get a better result for parsing instead of a construction query:

sqlite> .tables
students
sqlite> .schema students
create table students(id INTEGER, name TEXT);
sqlite> pragma table_info(students);
0|id|INTEGER|0||0
1|name|TEXT|0||0

Hope, it helps to some extent...

Answer 4

Another useful trick is to first get all the table names from sqlite_master.

Then for each one, fire off a query "select * from t where 1 = 0". If you analyze the structure of the resulting query - depends on what language/api you're calling it from - you get a rich structure describing the columns.

In python

c = ...db.cursor()
c.execute("select * from t where 1=0");
c.fetchall();
print c.description;

Juraj

PS. I'm in the habit of using 'where 1=0' because the record limiting syntax seems to vary from db to db. Furthermore, a good database will optimize out this always-false clause.

The same effect, in SQLite, is achieved with 'limit 0'.

Answer 5

Try this sqlite table schema parser, I implemented the sqlite table parser for parsing the table definitions in PHP.

It returns the full definitions (unique, primary key, type, precision, not null, references, table constraints... etc)

https://github.com/maghead/sqlite-parser

The syntax follows sqlite create table statement syntax: http://www.sqlite.org/lang_createtable.html

Answer 6

FYI, if you're using .Net you can use the DbConnection.GetSchema method to retrieve information that usually is in INFORMATION_SCHEMA. If you have an abstraction layer you can have the same code for all types of databases (NOTE that MySQL seems to swich the 1st 2 arguments of the restrictions array).

Answer 7

This is an old question but because of the number of times it has been viewed we are adding to the question for the simple reason most of the answers tell you how to find the TABLE names in the SQLite Database WHAT DO YOU DO WHEN THE TABLE NAME IS NOT IN THE DATABASE ? This is happening to our app because we are creating TABLES programmatically So the code below will deal with the issue when the TABLE is NOT in or created by the Database Enjoy

    public void toPageTwo(View view){

    if(etQuizTable.getText().toString().equals("")){
        Toast.makeText(getApplicationContext(), "Enter Table Name\n\n"
                +"           OR"+"\n\nMake Table First", Toast.LENGTH_LONG 
   ).show();
        etQuizTable.requestFocus();
        return;
    }

    NEW_TABLE = etQuizTable.getText().toString().trim();
    db = dbHelper.getWritableDatabase();
    ArrayList<String> arrTblNames = new ArrayList<>();
    Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE 
   type='table'", null);

    if (c.moveToFirst()) {
        while ( !c.isAfterLast() ) {
            arrTblNames.add( c.getString( c.getColumnIndex("name")) );
            c.moveToNext();
        }
    }
    c.close();
    db.close();

    boolean matchFound = false;
    for(int i=0;i<arrTblNames.size();i++) {
        if(arrTblNames.get(i).equals(NEW_TABLE)) {
            Intent intent = new Intent(ManageTables.this, TableCreate.class 
   );
            startActivity( intent );
            matchFound = true;
        }
    }
    if (!matchFound) {
        Toast.makeText(getApplicationContext(), "No Such Table\n\n"
                +"           OR"+"\n\nMake Table First", Toast.LENGTH_LONG 
 ).show();
        etQuizTable.requestFocus();
    }
}