612

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.

This Stackoverflow entry suggests doing the select

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

and parse the result.

Is that the common way? Alternatives?

2

25 Answers 25

848
PRAGMA table_info(table_name);

will get you a list of all the column names.

10
  • 27
    but you can't select from that table. It's just plain annoying. I'm trying something like this... but it don't work create temporary table TmpCols (cid integer, name text, type text, nn bit, dflt_value, pk bit); .mode insert TmpCols .output cols PRAGMA TABLE_INFO('yourtable'); .read cols .mode csv .output stdout
    – Jason
    Commented Jan 5, 2012 at 7:03
  • Just to put this into code terms for SQLiteDatabase on Android, write db.rawQuery("PRAGMA table_info(" + tablename + ")", null);
    – Noumenon
    Commented Jun 8, 2013 at 14:14
  • 6
    This will also work in case of View. PRAGMA table_info(View_Name); This will list all columns of a View
    – user1032317
    Commented Jul 26, 2013 at 3:00
  • 1
    Why is it called a Pragma? Aren't those used to hack the compiler? Wtf is it doing here lol?
    – rassa45
    Commented Sep 1, 2018 at 23:40
  • 17
    To execute as a query, see answer from @user1461607: select * from pragma_table_info('tblName') as tblInfo;
    – mdisibio
    Commented Sep 5, 2020 at 2:03
371

If you do

.headers ON

you will get the desired result.

6
  • 7
    how to align up headers with content below?
    – eastwater
    Commented Feb 26, 2018 at 16:53
  • 28
    And to always have that on, put it in your .sqliterc file.
    – ruffin
    Commented Mar 7, 2018 at 14:13
  • 2
    Should this work with an empty table? I am still not seeing column names Commented Nov 27, 2018 at 16:26
  • For some reasons I don't know, the PRAGMA method and the .schema method both didn't work for me. But this one works just fine. Commented Feb 21, 2019 at 23:43
  • 28
    .headers on and .mode columns will turn on columns names and align everything Commented Sep 28, 2021 at 15:18
315

If you have the sqlite database, use the sqlite3 command line program and these commands:

To list all the tables in the database:

.tables

To show the schema for a given tablename:

.schema tablename
2
  • 11
    Although the output isn't as "readable" (perhaps) this is_much_ easier to remember than PRAGMA table_info(table_name); Commented Sep 14, 2014 at 21:24
  • 15
    @NickTomlin Unfortunately, this method requires having the sqlite3 command line program, as dot commands are not valid SQL.
    – Michael
    Commented Feb 19, 2015 at 17:49
149

Just for super noobs like me wondering how or what people meant by

PRAGMA table_info('table_name') 

You want to use use that as your prepare statement as shown below. Doing so selects a table that looks like this except is populated with values pertaining to your table.

cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     99                      1         
1           name                    0                       0

Where id and name are the actual names of your columns. So to get that value you need to select column name by using:

//returns the name
sqlite3_column_text(stmt, 1);
//returns the type
sqlite3_column_text(stmt, 2);

Which will return the current row's column's name. To grab them all or find the one you want you need to iterate through all the rows. Simplest way to do so would be in the manner below.

//where rc is an int variable if wondering :/
rc = sqlite3_prepare_v2(dbPointer, "pragma table_info ('your table name goes here')", -1, &stmt, NULL);

if (rc==SQLITE_OK)
{
    //will continue to go down the rows (columns in your table) till there are no more
    while(sqlite3_step(stmt) == SQLITE_ROW)
    {
        sprintf(colName, "%s", sqlite3_column_text(stmt, 1));
        //do something with colName because it contains the column's name
    }
}
2
  • 1
    What they meant by that is to execute sqlite3 (or whatever it is named for you) to go into the sqlite CLI and then type in that text. No need to write extensive code for that :)
    – xeruf
    Commented Aug 13, 2020 at 8:53
  • Yes, as @Xerus says... no need for extensive code. Just use sqlite3 directly. Also, @birdbuster, it helps to specify the language and library you are using. It looks to me like C++ (from the sprintf function). It is helpful to clarify, since the OP question was language-agnostic. Commented Sep 30, 2020 at 14:57
133

If you want the output of your queries to include columns names and be correctly aligned as columns, use these commands in sqlite3:

.headers on
.mode column

You will get output like:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from mytable;
id          foo         bar
----------  ----------  ----------
1           val1        val2
2           val3        val4
2
  • 4
    And put those in ~/.sqliterc. Commented Mar 20, 2023 at 7:42
  • All terminal commands an no SQL.
    – Ash
    Commented Aug 1, 2023 at 17:42
83

An alternative way to get a list of column names not mentioned here that is cross platform and does not rely on the sqlite3.exe shell is to select from the PRAGMA_TABLE_INFO() table value function.

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name      
tbl_name  
rootpage  
sql

You can check if a certain column exists by querying:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1

This is what you use if you don't want to parse the result of select sql from sqlite_master or pragma table_info.

Note this feature is experimental and was added in SQLite version 3.16.0 (2017-01-02).

Reference:

https://www.sqlite.org/pragma.html#pragfunc

3
  • 1
    This only works for local db. if you try this with attached schemas, it won't work. Commented Dec 7, 2021 at 5:45
  • @JinghuiNiu It works if you pass the schema as the second argument, pragma_table_info('table', 'schema'). Commented Sep 22, 2023 at 12:46
  • This is the best / accurate way to retrieve column names ONLY.
    – Ghulam
    Commented Feb 5 at 16:42
39

To get a list of columns you can simply use:

.schema tablename
1
  • 6
    This will not show columns added with the ALTER statement.
    – RajeshM
    Commented Jan 11, 2020 at 0:31
24

I know it is an old thread, but recently I needed the same and found a neat way:

SELECT c.name FROM pragma_table_info('your_table_name') c;
4
  • 1
    You did mean: where t.name = 'table';
    – Luuk
    Commented Mar 23, 2020 at 18:25
  • 5
    did you find the neat way from my answer? 😂 Commented May 10, 2020 at 9:00
  • I think the c has him confused. Gonna be a long journey for this one. Commented Dec 4, 2023 at 22:14
  • The answer could need an explanation: After all it should be an answer, and not a "guess what this code does" riddle.
    – U. Windl
    Commented Feb 15 at 8:56
21

When you run the sqlite3 cli, typing in:

sqlite3 -header

will also give the desired result

1
  • Weak answer. It's a CLI command. SQL query is expected.
    – Ash
    Commented Aug 1, 2023 at 17:42
18

These commands below can set column names:

.headers on
.header on

Then, you can get the result with column names as shown below:

sqlite> SELECT * FROM user;
id|first_name|last_name|age
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

And, these commands below can unset column names:

.headers off
.header off

Then, you can get the result without column names as shown below:

sqlite> SELECT * FROM user;
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

And, these commands below can show the details of the command .headers:

.help .headers
.help .header
.help headers
.help header

Then, you can show the details of the command .headers as shown below:

sqlite> .help .headers
.headers on|off          Turn display of headers on or off

In addition, this command below can set the output mode box:

.mode box

Then, you can set the output mode box as shown below:

sqlite> SELECT * FROM user;
┌────┬────────────┬────────────┬─────┐
│ id │ first_name │ last_name  │ age │
├────┼────────────┼────────────┼─────┤
│ 1  │ Steve      │ Jobs       │ 56  │
│ 2  │ Bill       │ Gates      │ 66  │
│ 3  │ Mark       │ Zuckerberg │ 38  │
└────┴────────────┴────────────┴─────┘

And, this command below sets the output mode table:

.mode table

Then, you can set the output mode table as shown below:

sqlite> SELECT * FROM user;
+----+------------+------------+-----+
| id | first_name | last_name  | age |
+----+------------+------------+-----+
| 1  | Steve      | Jobs       | 56  |
| 2  | Bill       | Gates      | 66  |
| 3  | Mark       | Zuckerberg | 38  |
+----+------------+------------+-----+

And, these commands can show the details of the command .mode:

.help .mode
.help mode
.help modes

Then, you can show the details of the command .mode:

sqlite> .help .mode
.import FILE TABLE       Import data from FILE into TABLE
   Options:
     --ascii               Use \037 and \036 as column and row separators
     --csv                 Use , and \n as column and row separators
     --skip N              Skip the first N rows of input
     --schema S            Target table to be S.TABLE
     -v                    "Verbose" - increase auxiliary output
   Notes:
     *  If TABLE does not exist, it is created.  The first row of input
        determines the column names.
     *  If neither --csv or --ascii are used, the input mode is derived
        from the ".mode" output mode
     *  If FILE begins with "|" then it is a command that generates the
        input text.
.mode MODE ?OPTIONS?     Set output mode
   MODE is one of:
     ascii       Columns/rows delimited by 0x1F and 0x1E
     box         Tables using unicode box-drawing characters
     csv         Comma-separated values
     column      Output in columns.  (See .width)
     html        HTML <table> code
     insert      SQL insert statements for TABLE
     json        Results in a JSON array
     line        One value per line
     list        Values delimited by "|"
     markdown    Markdown table format
     qbox        Shorthand for "box --width 60 --quote"
     quote       Escape answers as for SQL
     table       ASCII-art table
     tabs        Tab-separated values
     tcl         TCL list elements
   OPTIONS: (for columnar modes or insert mode):
     --wrap N       Wrap output lines to no longer than N characters
     --wordwrap B   Wrap or not at word boundaries per B (on/off)
     --ww           Shorthand for "--wordwrap 1"
     --quote        Quote output text as SQL literals
     --noquote      Do not quote output text
     TABLE          The name of SQL table used for "insert" mode

Lastly, you can show the commands .headers and .mode with .help as shown below:

sqlite> .help     
...
.headers on|off          Turn display of headers on or off
...
.mode MODE ?OPTIONS?     Set output mode
...
15

.schema table_name

This will list down the column names of the table from the database.

Hope this will help!!!

10

This is an old question, but here is an alternative answer that retrieves all the columns in the SQLite database, with the name of the associated table for each column :

WITH tables AS (SELECT name tableName, sql 
FROM sqlite_master WHERE type = 'table' AND tableName NOT LIKE 'sqlite_%')
SELECT fields.name, fields.type, tableName
FROM tables CROSS JOIN pragma_table_info(tables.tableName) fields

This returns this type of result:

{
    "name": "id",
    "type": "integer",
    "tableName": "examples"
}, {
    "name": "content",
    "type": "text",
    "tableName": "examples"
}

For a simple table containing an identifier and a string content.

1
  • Nice - this allows you to search all tables / fields. In my case, I needed to find all tables with some common field names. Commented Aug 13, 2022 at 22:34
9

you can use Like statement if you are searching for any particular column

ex:

SELECT * FROM sqlite_master where sql like('%LAST%')
7

In order to get the column information you can use the following snippet:

String sql = "select * from "+oTablename+" LIMIT 0";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ResultSetMetaData mrs = rs.getMetaData();
for(int i = 1; i <= mrs.getColumnCount(); i++)
{
    Object row[] = new Object[3];
    row[0] = mrs.getColumnLabel(i);
    row[1] = mrs.getColumnTypeName(i);
    row[2] = mrs.getPrecision(i);
}
3
  • this works with views, joins, etc. -- but what db wrapper is this? Commented May 20, 2015 at 21:07
  • It's simply jdbc. No wrapper.
    – Devolus
    Commented May 21, 2015 at 8:47
  • 2
    jdbc is a wrapper ;)
    – xeruf
    Commented Aug 13, 2020 at 8:55
6
//JUST little bit modified the answer of giuseppe  which returns array of table columns
+(NSMutableArray*)tableInfo:(NSString *)table{

    sqlite3_stmt *sqlStatement;

    NSMutableArray *result = [NSMutableArray array];

    const char *sql = [[NSString stringWithFormat:@"PRAGMA table_info('%@')",table] UTF8String];

    if(sqlite3_prepare(md.database, sql, -1, &sqlStatement, NULL) != SQLITE_OK)

    {
        NSLog(@"Problem with prepare statement tableInfo %@",
                [NSString stringWithUTF8String:(const char *)sqlite3_errmsg(md.database)]);

    }

    while (sqlite3_step(sqlStatement)==SQLITE_ROW)
    {
        [result addObject:
          [NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];
    }

    return result;
}
6

.schema in sqlite console when you have you're inside the table it looks something like this for me ...

sqlite>.schema
CREATE TABLE players(
id integer primary key,
Name varchar(255),
Number INT,
Team varchar(255)
4
function getDetails(){
var data = [];
dBase.executeSql("PRAGMA table_info('table_name') ", [], function(rsp){
    if(rsp.rows.length > 0){
        for(var i=0; i<rsp.rows.length; i++){
            var o = {
                name: rsp.rows.item(i).name,
                type: rsp.rows.item(i).type
            } 
            data.push(o);
        }
    }
    alert(rsp.rows.item(0).name);

},function(error){
    alert(JSON.stringify(error));
});             
}
1
  • 2
    Hey, I think the question was about the SQLite CLI. Please, add least add an explanation.
    – xeruf
    Commented Aug 13, 2020 at 8:50
4

I know it's too late but this will help other.

To find the column name of the table, you should execute select * from tbl_name and you will get the result in sqlite3_stmt *. and check the column iterate over the total fetched column. Please refer following code for the same.

// sqlite3_stmt *statement ;
int totalColumn = sqlite3_column_count(statement);
for (int iterator = 0; iterator<totalColumn; iterator++) {
   NSLog(@"%s", sqlite3_column_name(statement, iterator));
}

This will print all the column names of the result set.

1
  • 1
    Hey, I think the question was about the SQLite CLI. You should mention which language you are using - is this plain C?
    – xeruf
    Commented Aug 13, 2020 at 8:51
3
-(NSMutableDictionary*)tableInfo:(NSString *)table
{
  sqlite3_stmt *sqlStatement;
  NSMutableDictionary *result = [[NSMutableDictionary alloc] init];
  const char *sql = [[NSString stringWithFormat:@"pragma table_info('%s')",[table UTF8String]] UTF8String];
  if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
  {
    NSLog(@"Problem with prepare statement tableInfo %@",[NSString stringWithUTF8String:(const char *)sqlite3_errmsg(db)]);

  }
  while (sqlite3_step(sqlStatement)==SQLITE_ROW)
  {
    [result setObject:@"" forKey:[NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];

  }

  return result;
  }
1

I was able to retrieve table names with corresponding columns by using one sql query, but columns output is comma separated. I hope it helps somebody

SELECT tbl_name, (SELECT GROUP_CONCAT(name, ',') FROM PRAGMA_TABLE_INFO(tbl_name)) as columns FROM sqlite_schema WHERE type = 'table';
1

For use in Python with sqlite3

Top answer PRAGMA table_info() returns a list of tuples, which might not be suitable for further processing, e.g.:

[(0, 'id', 'INTEGER', 0, None, 0),
 (1, 'name', 'TEXT', 0, None, 0),
 (2, 'age', 'INTEGER', 0, None, 0),
 (3, 'profession', 'TEXT', 0, None, 0)]

When using sqlite3 in Python, simply add a list comprehension in the end to filter out unwanted information.

import sqlite3 as sq

def col_names(t_name):
    with sq.connect('file:{}.sqlite?mode=ro'.format(t_name),uri=True) as conn:
        cursor = conn.cursor()
        cursor.execute("PRAGMA table_info({}) ".format(t_name))
        data = cursor.fetchall()
        return [i[1] for i in data]

col_names("your_table_name")

Result

["id","name","age","profession"]

DISCLAIMER: Do not use in production as this snippet is subject to possible SQL injection!

1

Get a list of tables and columns as a view:

CREATE VIEW Table_Columns AS
SELECT m.tbl_name AS TableView_Name, m.type AS TableView, cid+1 AS Column, p.*
FROM sqlite_master m, Pragma_Table_Info(m.tbl_name) p
WHERE m.type IN ('table', 'view') AND
   ( m.tbl_name = 'mypeople' OR m.tbl_name LIKE 'US_%')   -- filter tables
ORDER BY m.tbl_name;
0
     //Called when application is started. It works on Droidscript, it is tested
     function OnStart()
     {
     //Create a layout with objects vertically centered. 
     lay = app.CreateLayout( "linear", "VCenter,FillXY" );  

     //Create a text label and add it to layout.
     txt = app.CreateText( "", 0.9, 0.4, "multiline" )  
     lay.AddChild( txt );
     app.AddLayout(lay);

     db = app.OpenDatabase( "MyData" )  
  
     //Create a table (if it does not exist already).  
     db.ExecuteSql( "drop table if exists test_table" )
     db.ExecuteSql( "CREATE TABLE IF NOT EXISTS test_table " +  
       "(id integer primary key, data text, num integer)",[],null, OnError )  
        db.ExecuteSql( "insert into test_table values (1,'data10',100), 
        (2,'data20',200),(3,'data30',300)")
        //Get all the table rows.      
        DisplayAllRows("SELECT * FROM test_table");
        DisplayAllRows("select *, id+100 as idplus, 'hahaha' as blabla from 
        test_table order by id desc;") 
     }

//function to display all records 
function DisplayAllRows(sqlstring)  // <-- can you use for any table not need to 
                                //  know column names, just use a *
                                // example: 
{ 
//Use all rows what is in ExecuteSql  (try any, it will works fine)
db.ExecuteSql( sqlstring, [], OnResult, OnError ) 
} 
//Callback to show query results in debug.  
function OnResult( res )   
{  
var len = res.rows.length; 
var s = txt.GetText();  
// ***********************************************************************
// This is the answer how to read column names from table:
for(var ColumnNames in res.rows.item(0)) s += " [ "+ ColumnNames +" ] "; // "[" & "]" optional, i use only in this demo 
// ***********************************************************************
//app.Alert("Here is all Column names what Select from your table:\n"+s);
s+="\n";
for(var i = 0; i < len; i++ )   
{  
    var rows = res.rows.item(i) 
    for (var item in rows) 
        {
            s += "    " + rows[item] + "   ";
        }
    s+="\n\n";
} 
//app.Alert(s);
txt.SetText( s )  
}  
//Callback to show errors.  
function OnError( msg )   
{  
   app.Alert( "Error: " + msg )  
}  
3
  • 1
    This answer is too long and overly verbose, instead of posting code, please add more detail as to how and why this presents a solution for the user, such that it can be read and understood without having to be parsed first Commented Sep 14, 2021 at 23:42
  • Hi, I just wanted share my idea, because i not fund complete resolved for my same problem before. There is the demo for the DroidScript. Enough a thanks or if you want I will delete my shared. Sorry my english. Commented Sep 15, 2021 at 4:00
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Sep 15, 2021 at 4:18
0

If you're using the SQLite3, INFORMATION_SCHEMA is not supported. Use PRAGMA table_info instead. This will return 6 rows of information about the table. To fetch the column name (row2), use a for loop like the following

cur.execute("PRAGMA table_info(table_name)")  # fetches the 6 rows of data
records = cur.fetchall() 
print(records)
for row in records:
    print("Columns: ", row[1])
0

I've not seen this elsewhere, but it seems to work for us: SELECT * FROM PRAGMA_table_list t JOIN PRAGMA_table_info(t.Name) c

1
  • What are the benefits of doing it this way instead of one of the shorter and more understandable suggestions in the other answers?
    – moo
    Commented May 31, 2023 at 5:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.