Is there an SQLite equivalent to MySQL's DESCRIBE [table]?

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?

score 606 · Accepted Answer · edited Feb 15 '22 at 15:42

606

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

edited Feb 15 '22 at 15:42

jcsahnwaldt Reinstate Monica

3,434
4
35
33

answered Jul 25 '10 at 18:27

Ned Batchelder

364,293
75
561
662

76

You can use ".schema" without a table and it'll show you all of them. – Dan Benamy Nov 30 '12 at 06:16
61

Don't forget to leave out the semi-colon at the end of the statement – RTF Aug 22 '13 at 11:18
13

A semi-colon is not required – Simon Feb 18 '14 at 16:43
47

@Simon a semi-colon will cause the command to silently *fail*. – djeikyb May 22 '14 at 22:27
6

@djeikyb. shouldn't this be considered a bug? – Makan Aug 18 '14 at 08:36
1

Possibly they fixed this, since at least in my version of SQLite (3.8.10.2), I can issue `.schema` and `.schema users` (for example) and the output seems to be correct---no semicolon needed for me. – therealrootuser Aug 29 '15 at 21:55
3

Note that `.schema` is a command for the `sqlite3`-shell and **NOT** valid SQL. Meaning if you want to get the table-info in an application, you have to use **@Strater**'s answer. – Lukas Knuth Apr 25 '16 at 08:19
Typing ".help " can help in finding useful commands like ".shema" – shane May 31 '17 at 07:15
Even though the OP said, "PRAGMA table_info [table] isn't good enough, as it only has basic information"? – Ned Batchelder Aug 07 '17 at 19:16

score 334 · Answer 2 · edited Dec 04 '14 at 08:10

334

PRAGMA table_info([tablename]);

edited Dec 04 '14 at 08:10

Alex Bitek

6,529
5
47
77

answered Oct 06 '11 at 18:56

Strater

3,373
1
13
2

29

This seems more equivalent to MySQL's describe than `.schema tablename` to me. – tybro0103 Jun 25 '12 at 20:34
3

Yep. This worked for me. .schema TABLENAME didn't. .schema alone, however, does show you all the create statements, but the result from PRAGMA is a lot more useful if I just want to look at one table. – Dev Kanchen Aug 07 '12 at 09:08
19

This seems like it should be the accepted answer since it works through querying instead of being dependent on a command line interface. +1 from me. – Akoi Meexx Sep 02 '12 at 20:37
1

Addendum: The only thing I'm noticing is that it does not output PRIMARY KEY when I create a table with INTEGER PRIMARY KEY, just INTEGER. – Akoi Meexx Sep 02 '12 at 20:47
6

@AkoiMeexx: From my original question: "`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)." – Matthew Feb 02 '13 at 23:40
It also displays the data in a more table like format rather than looking at the CREATE statement from the .schema command – OzzyTheGiant Sep 27 '16 at 20:30

score 128 · Answer 3 · edited Nov 24 '21 at 21:48

128

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

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

Alternative Names

The schema table can always be referenced using the name sqlite_schema, especially if qualifed by the schema name like main.sqlite_schema or temp.sqlite_schema. But for historical compatibility, some alternative names are also recognized, including:

sqlite_master

sqlite_temp_schema

sqlite_temp_master

Alternatives (2) and (3) only work for the TEMP database associated with each database connection, but alternative (1) works anywhere.

edited Nov 24 '21 at 21:48

Cristian Ciupitu

20,270
7
50
76

answered Jul 25 '10 at 18:29

Mark Rushakoff

249,864
45
407
398

1

Is there any difference between this and `.schema foo`? – Matthew Jul 25 '10 at 18:45
23

@Matthew: `.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
1

@MarkRushakoff But the result is the same? – Dvir Berebi Mar 07 '12 at 10:59
3

+1 "SELECT * FROM sqlite_master" works in chrome dev tools when debugging WebSql – contactmatt Aug 15 '14 at 21:36

score 61 · Answer 4 · answered Jul 25 '10 at 18:28

61

To see all tables:

.tables

To see a particular table:

.schema [tablename]

answered Jul 25 '10 at 18:28

Ross Snyder

1,945
11
11

Giving table name inside [square bracket] doesn't work. You have to give `.schema TABLENAME` – Payel Senapati Jul 21 '21 at 17:17
1

@PayelSenapati The brackets are just to indicate that `tablename` is an optional parameter. – luckman212 Dec 29 '21 at 18:04

score 9 · Answer 5 · edited Apr 21 '19 at 17:20

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

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

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

Anybody writing a program to retrieve data from any SQL database should use the proper SQL drivers available to their programming language for accessing the database and performing queries on it. That is the appropriate way to access a database. I would never recommend hacking a command-line program designed to provide ad-hoc queries. Your suggestion is deeply mistaken. A command-line program for ad-hoc queries is CERTAINLY NOT the most appropriate access point for program code to run queries on a database. Using SQL drivers is CERTAINLY NOT 'complicating maintenance' - it is best practice. — Medlock Perlman, Nov 16 '17 at 13:11
I agree it is no bad, it is similar to libraries. Which is why Linux|BSD distros ship package managers. And why there is 0install cross platform PM. My point was just to clarify that not all programs need wrappers. It does not make sense everytime. In this case (DB handling) of course it not a bad idea to use a wrapper. — Radagast, Nov 22 '17 at 19:10

score 4 · Answer 6 · edited Feb 09 '23 at 05:29

".schema" can show more details of tables including Table Constraints than "PRAGMA".

This command below shows the details of all tables:

.schema

This command below shows the details of all tables in a well-formatted way:

.schema --indent

This command below shows the details of one table:

.schema <table_name>

These commands below show the details of one table in a well-formatted way:

.schema --indent <table_name>

Or:

.schema <table_name> --indent

In addition, these commands below show the details about ".schema":

.help .schema

Or:

.help schema

Then, this is how it looks like below:

sqlite> .help .schema 
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
   Options:
      --indent             Try to pretty-print the schema
      --nosys              Omit objects whose names start with "sqlite_"

score 2 · Answer 7 · answered Aug 01 '19 at 08:20

If you're using a graphical tool. It shows you the schema right next to the table name. In case of DB Browser For Sqlite, click to open the database(top right corner), navigate and open your database, you'll see the information populated in the table as below.

right click on the record/table_name, click on copy create statement and there you have it.

Hope it helped some beginner who failed to work with the commandline.

score 0 · Answer 8 · answered May 14 '23 at 02:27

0

For example, I have these tables in my Django SQLite database:

sqlite3 db.sqlite3

Then

.tables

In order to describe and see the SQL statements for shop_product table, you can run the following command:

.schema shop_product

Illustration

answered May 14 '23 at 02:27

ahmnouira

1,607
13
8

Is there an SQLite equivalent to MySQL's DESCRIBE [table]?

8 Answers8

Alternative Names

Illustration

Linked

Related