Is there an SQLite equivalent to MySQL's DESCR

Question

I'm just getting started learning SQLite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic information (for example, it doesn't show if a column is a field of some sort or not). Does SQLite have a way to do this?

Answer 1

To prevent that people are mislead by some of the comments to the other answers:

1. If .schema or query from sqlite_master not gives any output, it indicates a non-existent tablename, e.g. this may also be caused by a ; semicolon at the end for .schema, .tables, ... Or just because the table really not exists. That .schema just doesn't work is very unlikely and then a bug report should be filed at the sqlite project.

... .schema can only be used from a command line; the above commands > can be run as a query through a library (Python, C#, etc.). – Mark Rushakoff Jul 25 '10 at 21:09

2. 'can only be used from a command line' may mislead people. Almost any (likely every?) programming language can call other programs/commands. Therefore the quoted comment is unlucky as calling another program, in this case sqlite, is more likely to be supported than that the language provides a wrapper/library for every program (which not only is prone to incompleteness by the very nature of the masses of programs out there, but also is counter acting single-source principle, complicating maintenance, furthering the chaos of data in the world).

(Put up as answer as no permission to comment yet.)

Answer 2

To see all tables:

.tables

To see a particular table:

.schema [tablename]

Answer 3

Are you looking for the SQL used to generate a table? For that, you can query the sqlite_master table:

sqlite> CREATE TABLE foo (bar INT, quux TEXT);
sqlite> SELECT * FROM sqlite_master;
table|foo|foo|2|CREATE TABLE foo (bar INT, quux TEXT)
sqlite> SELECT sql FROM sqlite_master WHERE name = 'foo';
CREATE TABLE foo (bar INT, quux TEXT)

Answer 4

The SQLite command line utility has a .schema TABLENAME command that shows you the create statements.

Answer 5

PRAGMA table_info([tablename]);