What SQL can be used to list the tables, and the rows within those tables, in a SQLite database file once i've ATTACHed it on the sqlite3 command line tool?
-
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.
izb : awesome, thanks. -
.tables
Will list the tables in your database
.schema tablename
will list how the table looks
and a
Select * from tablename
will print the entire table..
FilmJ : FYI: for a list of all the commands understood, try ".help" at your sqlite3 prompt. -
To list the tables you can also do:
SELECT name FROM sqlite_master WHERE type='table';
-
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);
-
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?
-
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
-
The
.tables
, and.schema
"helper" functions don't look into ATTACHed databases: they just query theSQLITE_MASTER
table for the "main" database. Consequently, if you usedATTACH 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 listsqlite_temp_master
for that:SELECT name FROM sqlite_temp_master WHERE type='table';
-
The easiest way to do this is to open the database directly and use the .dump command, rather than attaching it after invoking the sqlite3 shell tool
So... (assume your o/s command line prompt is $) instead of
$sqlite3
sqlite3> ATTACH database.sqlite as "attached"
from your o/s command line, open the database directly
$sqlite3 database.sqlite
sqlite3> .dump
-
Try PRAGMA table_info(table-name): http://www.sqlite.org/pragma.html#schema
-
select * from table table_name;
0 comments:
Post a Comment