Is there an SQLite equivalent to MySQL's DESCRIBE [table]?
MysqlSqliteMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
The SQLite command line utility has a .schema TABLENAME
command that shows you the create statements.
Solution 2 - Mysql
PRAGMA table_info([tablename]);
Solution 3 - Mysql
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:
>
> 1. sqlite_master
> 2. sqlite_temp_schema
> 3. sqlite_temp_master
>
> Alternatives (2) and (3) only work for the TEMP database associated with each database connection, but alternative (1) works anywhere.
Solution 4 - Mysql
To see all tables:
.tables
To see a particular table:
.schema [tablename]
Solution 5 - Mysql
To prevent that people are mislead by some of the comments to the other answers:
- If
.schema
orquery from sqlite_master
not gives any output, it indicates a non-existenttablename
, 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 awrapper
/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 actingsingle-source principle
, complicatingmaintenance
, furthering the chaos of data in the world).
Solution 6 - Mysql
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.
Solution 7 - Mysql
".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_"