How to get a list of column names on Sqlite3 database?

sql database sqlite select database-table

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?

For the specific case of SQLite.swift, see this question and answer for a simple list of column names or this one for migration issues.

Possible duplicate of How to get a list of column names

nevan king

PRAGMA table_info(table_name);

will get you a list of all the column names.

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

Just to put this into code terms for SQLiteDatabase on Android, write db.rawQuery("PRAGMA table_info(" + tablename + ")", null);

This will also work in case of View. PRAGMA table_info(View_Name); This will list all columns of a View

why not just stick "limit 0" on the end of a select statement? int cols = sqlite3_column_count(stmt); fprintf(stdout, "%d columns\n", cols); for (int i=0; i

To execute as a query, see answer from @user1461607: select * from pragma_table_info('tblName') as tblInfo;

George Hilliard

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

Although the output isn't as "readable" (perhaps) this is_much_ easier to remember than PRAGMA table_info(table_name);

@NickTomlin Unfortunately, this method requires having the sqlite3 command line program, as dot commands are not valid SQL.

slfan

If you do

.headers ON

you will get the desired result.

how to align up headers with content below?

And to always have that on, put it in your .sqliterc file.

Should this work with an empty table? I am still not seeing column names

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.

.headers on and .mode columns will turn on columns names and align everything

Birdbuster

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
    }
}

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 :)

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.

Owen Pauling

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

user1461607

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

This only works for local db. if you try this with attached schemas, it won't work.

Book Of Zeus

To get a list of columns you can simply use:

.schema tablename

This will not show columns added with the ALTER statement.

S.M.Mousavi

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;

You did mean: where t.name = 'table';

did you find the neat way from my answer? 😂

Sam Houston

When you run the sqlite3 cli, typing in:

sqlite3 -header

will also give the desired result

Sakthi Velan

.schema table_name

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

Hope this will help!!!

BoltClock

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

ex:

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

Devolus

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);
}

this works with views, joins, etc. -- but what db wrapper is this?

It's simply jdbc. No wrapper.

jdbc is a wrapper ;)

Kevin

//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;
}

Kevin Hu

.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)

Om Shankar

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));
});             
}

Hey, I think the question was about the SQLite CLI. Please, add least add an explanation.

Quentin

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.

gdm

-(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;
  }

taras

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.

Hey, I think the question was about the SQLite CLI. You should mention which language you are using - is this plain C?

Kai - Kazuya Ito

This command below sets column names:

.header on