1377

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?

5
  • 2
    try this one you got full info of tables http://www.sqlite.org/pragma.html#schema
    – Piyush
    Sep 9, 2011 at 7:39
  • 3
    The following is a useful GUI for sqlite if you are interested: sqlitestudio.pl Gives you access to view the details of the databases, tables, very quickly and has a nice query editor too... Apr 17, 2013 at 15:40
  • 24
    .tables for tables and .schema ?TABLE? for the schema of the specific table.
    – H6_
    Jun 16, 2013 at 15:52
  • .table 'bank_%' or .table '%_empl' also valid syntax for quering prefixes/suffixes!
    – gavenkoa
    Feb 20, 2015 at 0:23
  • @H6. I found that both, .tables and .schema deliver SQL (including original formatting and comments) that is often not as useful for further processing. To get the exact metadata, use pragmas or pragma functions, my answer demonstrates the use of both.
    – Wolf
    Jul 14, 2023 at 9:42

19 Answers 19

1402

There are a few steps to see the tables in an SQLite database:

  1. List the tables in your database:

    .tables
    
  2. List how the table looks:

    .schema tablename
    
  3. Print the entire table:

    SELECT * FROM tablename;
    
  4. List all of the available SQLite prompt commands:

    .help
    
6
  • 56
    .table and .tables are both allowed. For that matter, .ta would work as well, since sqlite3 will accept any command that is unambiguous. The name of the command according to the help is indeed ".tables" (if anyone is still paying attention).
    – dbn
    Feb 6, 2013 at 1:26
  • 8
    .tables won't display tables if one opened database(s) through ATTACH '<path>' AS <name>;but lasse's answer will do. since the OP mentioned ATTACHing i believe he was right in not accepting this answer. edit: just noticed that anthony and others below also pointed this out.
    – antiplex
    Mar 28, 2013 at 21:33
  • 2
    @dbw: Not necesserily. Consider you are doing a DB wrapper able to use SQLite or MySql (my case). Using more SQL-conform commands would make it easier to port the wrapped in other languages then if you'd use DB-vendor specific commands.
    – Valentin H
    Apr 8, 2013 at 8:42
  • 2
    This answer is missing context, so it's not helpful.
    – NL23codes
    Jun 22, 2020 at 14:49
  • 1
    sqlite3.OperationalError: no such table: tablename
    – greendino
    Mar 8, 2023 at 4:22
682

The .tables, and .schema "helper" functions don't look into ATTACHed databases: they just query the SQLITE_MASTER table for the "main" database. Consequently, if you used

ATTACH some_file.db AS my_db;

then you need to do

SELECT name FROM my_db.sqlite_master WHERE type='table';

Note that temporary tables don't show up with .tables either: you have to list sqlite_temp_master for that:

SELECT name FROM sqlite_temp_master WHERE type='table';
6
  • 163
    Only "SELECT name FROM sqlite_master WHERE type='table'" works for me
    – vladkras
    Dec 15, 2015 at 13:28
  • 6
    SELECT name FROM my_db.sqlite_master WHERE type='table'; this does not work for me (for the attached DB) and it throws error as: no such table exist "my_db.sqlite_master"
    – kanika
    Jul 27, 2016 at 7:16
  • what you meant by temporary tables? Are there any when I just opened SQLite db file?
    – Ewoks
    May 7, 2017 at 13:20
  • Temporary tables are those created with CREATE TEMPORARY TABLE SQL commands. Their contents are dropped when the current database connection is closed, and they are never saved to a database file. May 8, 2017 at 14:37
  • 2
    Under sqlite3 command mode and run ATTACH "some_file.db" AS my_db; It worked!
    – John Jang
    Dec 25, 2017 at 7:22
471

It appears you need to go through the sqlite_master table, like this:

SELECT * FROM dbname.sqlite_master WHERE type='table';

And then manually go through each table with a SELECT or similar to look at the rows.

The .DUMP and .SCHEMA commands doesn't appear to see the database at all.

3
  • 123
    Not something easy to read or remember for use in the future; the builtin .tables command is more intuitive
    – user649198
    Feb 23, 2013 at 22:02
  • 27
    @Gryllida: despite this is usable from any SQL-API as it's valide SQL. Built-in commands may not be supported everywhere.
    – Valentin H
    Apr 8, 2013 at 8:36
  • you're just reading from sqlite_master table from column type where row is table right? I'd suggest you simply add that in your answer to make everyone better understand the structure of a database and its master table
    – mfaani
    Apr 26, 2016 at 20:53
194

To show all tables, use

SELECT name FROM sqlite_master WHERE type = "table"

To show all rows, I guess you can iterate through all tables and just do a SELECT * on each one. But maybe a DUMP is what you're after?

1
  • Also, this prints one table name per line, while .tables prints multiple columns of table names (annoying/not useful).
    – Shane
    Mar 21, 2014 at 19:14
75

Use .help to check for available commands.

.table

This command would show all tables under your current database.

2
  • 1
    Strange, it must be correct, but doesn't work when i use it
    – Jürgen K.
    Jan 14, 2016 at 15:54
  • @Jürgen K.: What happened? Aug 6, 2021 at 15:58
51

There is a command available for this on the SQLite command line:

.tables ?PATTERN?      List names of tables matching a LIKE pattern

Which converts to the following SQL:

SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1
42

To list the tables you can also do:

SELECT name FROM sqlite_master
WHERE type='table';
2
  • So... cur.execute(""" SELECT name FROM sqlite_master WHERE type='table';""") or no? That's not working for me, but I'm not sure where this code is supposed to be run.
    – jbuddy_13
    Apr 23, 2020 at 15:32
  • Hmm. I'm running these "dot" commands in DB Browser For Sqlite and they do not work. May 18, 2022 at 20:48
39

I use this query to get it:

SELECT name FROM sqlite_master WHERE type='table'

And to use in iOS:

NSString *aStrQuery=[NSString stringWithFormat:@"SELECT name FROM sqlite_master WHERE type='table'"];
36

Try PRAGMA table_info(table-name);
http://www.sqlite.org/pragma.html#schema

1
  • 8
    This only works if you know the name of the table. You can't use this to get the list of table names.
    – Eric W
    Apr 18, 2013 at 14:06
21

According to the documentation, the equivalent of MySQL's SHOW TABLES; is:

The ".tables" command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master
  WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
  WHERE type IN ('table','view')
ORDER BY 1;

However, if you are checking if a single table exists (or to get its details), see LuizGeron's answer.

18

As of the latest versions of SQLite 3 you can issue:

.fullschema

to see all of your create statements.

2
  • SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .fullschema Error: unknown command or invalid arguments: "fullschema". Enter ".help" for help
    – Mona Jalal
    Oct 14, 2014 at 19:06
  • 2
    You are using a version from 2012
    – pepper
    Oct 14, 2014 at 19:08
13

The easiest way to do this is to open the database directly and use the .dump command, rather than attaching it after invoking the SQLite 3 shell tool.

So... (assume your OS command line prompt is $) instead of $sqlite3:

sqlite3> ATTACH database.sqlite as "attached"

From your OS command line, open the database directly:

$sqlite3 database.sqlite
sqlite3> .dump
12

Via a union all, combine all tables into one list.

select name
from sqlite_master 
where type='table'

union all 

select name 
from sqlite_temp_master 
where type='table'
12

Use .da to see all databases - one is called 'main'.

Tables of this database can be seen by:

SELECT distinct tbl_name from sqlite_master order by 1;

The attached databases need prefixes you chose with AS in the statement ATTACH, e.g., aa (, bb, cc...) so:

SELECT distinct tbl_name from **aa.sqlite_master** order by 1;

Note that here you get the views as well. To exclude these add:

where type = 'table'

before ' order'

11

Use:

import sqlite3

TABLE_LIST_QUERY = "SELECT * FROM sqlite_master where type='table'"
10

Since nobody has mentioned about the official reference of SQLite, I think it may be useful to refer to it under this heading:

https://www.sqlite.org/cli.html

You can manipulate your database using the commands described in this link. Besides, if you are using Windows OS and do not know where the command shell is, that is in the SQLite's site:

https://www.sqlite.org/download.html

After downloading it, click sqlite3.exe file to initialize the SQLite command shell. When it is initialized, by default this SQLite session is using an in-memory database, not a file on disk, and so all changes will be lost when the session exits. To use a persistent disk file as the database, enter the ".open ex1.db" command immediately after the terminal window starts up.

The example above causes the database file named "ex1.db" to be opened and used, and created if it does not previously exist. You might want to use a full pathname to ensure that the file is in the directory that you think it is in. Use forward-slashes as the directory separator character. In other words use "c:/work/ex1.db", not "c:\work\ex1.db".

To see all tables in the database you have previously chosen, type the command .tables as it is said in the above link.

If you work in Windows, I think it might be useful to move this sqlite.exe file to same folder with the other Python files. In this way, the Python file writes to and the SQLite shell reads from .db files are in the same path.

7

The ".schema" commando will list available tables and their rows, by showing you the statement used to create said tables:

sqlite> create table_a (id int, a int, b int);
sqlite> .schema table_a
CREATE TABLE table_a (id int, a int, b int);
2

To get a list of tables in a SQLite database, you can use a simple SQL query. In SQLite, there's a table called sqlite_master that stores metadata about the database schema, including the table names. You can query this table to retrieve the names of all tables in the database.

Let's assume, for example, we have two tables, table1 and table2 in the SQLite database.

Here's the SQL query to fetch the list of tables in the SQLite database:

SELECT name FROM sqlite_master WHERE type='table';

output:

[('table1',), ('sqlite_sequence',), ('table2',)]

Filter the results to only include entries of type 'table' while excluding internal SQLite tables (those starting with sqlite_).

SELECT name FROM sqlite_master 
WHERE type='table' AND name NOT LIKE 'sqlite_%';

output:

[('table1',), ('table2',)]
0

Since the questioner did not provide a minimal reproducible example, I'll include a possible one in the following steps:

ATTACH DATABASE 'file::memory:' AS aux;
CREATE TABLE aux.numbers (v INT, name TEXT);
PRAGMA aux.table_xinfo(numbers);

Like all pragmas, pragma table_xinfo can also be used in form of a pragma function. We select its results from pragma_table_xinfo, please note that the table name argument must be a string literal here.

SELECT * FROM aux.pragma_table_xinfo('numbers');

Here is the output of both query variants (jazzed up by .mode box):

┌─────┬──────┬──────┬─────────┬────────────┬────┬────────┐
│ cid │ name │ type │ notnull │ dflt_value │ pk │ hidden │
├─────┼──────┼──────┼─────────┼────────────┼────┼────────┤
│ 0   │ v    │ INT  │ 0       │            │ 0  │ 0      │
│ 1   │ name │ TEXT │ 0       │            │ 0  │ 0      │
└─────┴──────┴──────┴─────────┴────────────┴────┴────────┘

You may be interested in the fact that you can get meta information about the pragma “tables” themselves, try pragma table_xinfo(pragma_xinfo); if you're curious.

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