Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?

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.
                @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, 2010 at 21:09
                Giving table name inside [square bracket] doesn't work. You have to give .schema TABLENAME
– Payel Senapati
                Jul 21, 2021 at 17:17
                @PayelSenapati The brackets are just to indicate that tablename is an optional parameter.
– luckman212
                Dec 29, 2021 at 18:04
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, 2017 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, 2017 at 19:10
".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>
.schema <table_name> --indent
In addition, these commands below show the details about ".schema":
.help .schema
.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_"
  
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.
.tables
In order to describe and see the SQL statements for shop_product table, you can run the following command:
.schema shop_product
Illustration
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.