How can I list the tables in a SQLite database file that was opened with ATTACH?


Sql Problem Overview

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 SQLite 3 command line tool?

Sql Solutions

Solution 1 - Sql

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

  1. List the tables in your database:


  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:


Solution 2 - Sql

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';

Solution 3 - Sql

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.

Solution 4 - Sql

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?

Solution 5 - Sql

Use .help to check for available commands.


This command would show all tables under your current database.

Solution 6 - Sql

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_%'
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')

Solution 7 - Sql

To list the tables you can also do:

SELECT name FROM sqlite_master
WHERE type='table';

Solution 8 - Sql

Try PRAGMA table_info(table-name);

Solution 9 - Sql

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'"];

Solution 10 - Sql

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_%'
SELECT name FROM sqlite_temp_master
  WHERE type IN ('table','view')

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

Solution 11 - Sql

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


to see all of your create statements.

Solution 12 - Sql

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

Solution 13 - Sql

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'

Solution 14 - Sql


import sqlite3

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

Solution 15 - Sql

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

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:

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.

Solution 16 - Sql

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'

Solution 17 - Sql

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


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionizbView Question on Stackoverflow
Solution 1 - SqlMark JanssenView Answer on Stackoverflow
Solution 2 - SqlAnthony WilliamsView Answer on Stackoverflow
Solution 3 - SqlLasse V. KarlsenView Answer on Stackoverflow
Solution 4 - SqlChristian DavénView Answer on Stackoverflow
Solution 5 - SqlAntony.HView Answer on Stackoverflow
Solution 6 - SqlflubbaView Answer on Stackoverflow
Solution 7 - SqlRafał DowgirdView Answer on Stackoverflow
Solution 8 - SqlLuiz GeronView Answer on Stackoverflow
Solution 9 - SqlGameLoadingView Answer on Stackoverflow
Solution 10 - SqlAlix AxelView Answer on Stackoverflow
Solution 11 - SqlpepperView Answer on Stackoverflow
Solution 12 - SqlNoahView Answer on Stackoverflow
Solution 13 - SqlopenwonkView Answer on Stackoverflow
Solution 14 - SqlMrityunjay SinghView Answer on Stackoverflow
Solution 15 - SqloiyioView Answer on Stackoverflow
Solution 16 - SqlKlaas-Z4us-VView Answer on Stackoverflow
Solution 17 - SqltrfView Answer on Stackoverflow