SQLite Schema Information Metadata

user_54929 0
Viewed 94114

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?

information-schema sqlite
7 Answers

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
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 (1 if the column is the table's primary key, else 0)

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

user_12680160

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();
    }
}
user_49329720
Related