标签云

微信群

扫码加入我们

WeChat QR Code

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


try this one you got full info of tables http://www.sqlite.org/pragma.html#schema

2019年06月25日01分15秒

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

2019年06月25日01分15秒

.tables for tables and .schema ?TABLE? for the schema of the specific table.

2019年06月25日01分15秒

.table 'bank_%' or .table '%_empl' also valid syntax for quering prefixes/suffixes!

2019年06月25日01分15秒

Only "SELECT name FROM sqlite_master WHERE type='table'" works for me

2019年06月26日01分15秒

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"

2019年06月25日01分15秒

what you meant by temporary tables? Are there any when I just opened SQLite db file?

2019年06月26日01分15秒

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.

2019年06月26日01分15秒

Under sqlite3 command mode and run ATTACH "some_file.db" AS my_db; It worked!

2019年06月25日01分15秒

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

2019年06月25日01分15秒

(This should be the accepted answer, it is the most sqlite-y way to do things).

2019年06月25日01分15秒

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

2019年06月25日01分15秒

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.

2019年06月25日01分15秒

"...sqlite3 will accept any command that is unambiguous..." Unfortunately sometimes it accepts ambiguous commands as well. For example, ".s" is interpreted as ".show", even though ".schema", '.separator", or ".stats" would be possibilities as well. And when it doesn't accept an ambiguous command, it doesn't list the possibilities.

2019年06月26日01分15秒

Not something easy to read or remember for use in the future; the builtin .tables command is more intuitive

2019年06月25日01分15秒

Gryllida: despite this is usable from any SQL-API as it's valide SQL. Built-in commands may not be supported everywhere.

2019年06月25日01分15秒

DoktorJ Have .tables been modified to display tables from an attached database?

2019年06月26日01分15秒

In that database, yes, but this question was about showing the tables in a database you have attached. Have the .tables command been modified to show those as well?

2019年06月26日01分15秒

Doh! Reading comprehension fail... I somehow managed to not catch the ATTACH reference... twice >_<

2019年06月25日01分15秒

Thanks for the only answer that really addressed the question... "What SQL", not what command can be used... thanks!

2019年06月26日01分15秒

Also, this prints one table name per line, while .tables prints multiple columns of table names (annoying/not useful).

2019年06月26日01分15秒

Strange, it must be correct, but doesn't work when i use it

2019年06月25日01分15秒

Where and how should I use .table???

2019年06月26日01分15秒

It's not working fro me also

2019年06月25日01分15秒

This is probably the best way to do it.

2019年06月26日01分15秒

This only works if you know the name of the table.You can't use this to get the list of table names.

2019年06月26日01分15秒

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

2019年06月25日01分15秒

You are using a version from 2012

2019年06月25日01分15秒