{"id":1473,"date":"2018-11-02T20:07:11","date_gmt":"2018-11-02T13:07:11","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1473"},"modified":"2022-04-03T15:06:21","modified_gmt":"2022-04-03T08:06:21","slug":"sqlite-describe-table","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-describe-table\/","title":{"rendered":"SQLite Describe Table"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn about various ways to show the structure of a table in SQLite.<\/p>\r\n\r\n\r\n\r\n

Getting the structure of a table via the SQLite command-line shell program<\/h2>\r\n\r\n\r\n\r\n

To find out the structure of a table via the SQLite command-line shell program, you follow these steps:<\/p>\r\n\r\n\r\n\r\n

First, connect to a database via the SQLite command-line shell program:<\/p>\r\n\r\n\r\n

sqlite3 c:\\sqlite\\db\\chinook.db<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Then, issue the following command:<\/p>\r\n\r\n\r\n
.schema table_name<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
For example, the following command shows the statement that created the albums<\/code> table:<\/p>\r\n\r\n\r\n
.schema albums<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Notice that there is no semicolon (;<\/code>) after the table name. If you add a semicolon (;<\/code>), the .schema<\/code> will consider the albums;<\/code> as the table name and returns nothing because the table albums;<\/code> does not exist.<\/p>\r\n\r\n\r\n\r\n
Here is the output:<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> \"albums\"<\/span>\r\n(\r\n    [AlbumId] INTEGER<\/span> PRIMARY KEY<\/span> AUTOINCREMENT NOT<\/span> NULL<\/span>,\r\n    [Title] NVARCHAR<\/span>(160<\/span>)  NOT<\/span> NULL<\/span>,\r\n    [ArtistId] INTEGER<\/span>  NOT<\/span> NULL<\/span>,\r\n    FOREIGN<\/span> KEY<\/span> ([ArtistId]) REFERENCES<\/span> \"artists\"<\/span> ([ArtistId])\r\n                ON<\/span> DELETE<\/span> NO<\/span> ACTION<\/span> ON<\/span> UPDATE<\/span> NO<\/span> ACTION<\/span>\r\n);\r\nCREATE<\/span> INDEX<\/span> [IFK_AlbumArtistId] ON<\/span> \"albums\"<\/span> ([ArtistId]);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Another way to show the structure of a table is to use the PRAGMA<\/code> command. To do it, you use the following command to format the output:<\/p>\r\n\r\n\r\n
.header on\r\n.mode column<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
And use the PRAGMA<\/code> command as follows:<\/p>\r\n\r\n\r\n
pragma table_info('albums'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following picture shows the output:<\/p>\r\n\r\n\r\n
cid  name      type           notnull  dflt_value  pk\r\n---  --------  -------------  -------  ----------  --\r\n0    AlbumId   INTEGER        1                    1\r\n1    Title     NVARCHAR(160)  1                    0\r\n2    ArtistId  INTEGER        1                    0<\/code><\/span><\/pre>\r\n\r\n\r\n
Getting the structure of a table using the SQL statement<\/h2>\r\n\r\n\r\n\r\n
You can find the structure of a table by querying it from the sqlite_schema<\/code> table as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span> sql<\/span> \r\nFROM<\/span> sqlite_schema \r\nWHERE<\/span> name<\/span> = 'albums'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Here is the output:<\/p>\r\n\r\n\r\n
sql\r\n------------<\/span>\r\nCREATE<\/span> TABLE<\/span> \"albums\"<\/span>\r\n(\r\n    [AlbumId] INTEGER<\/span> PRIMARY KEY<\/span> AUTOINCREMENT NOT<\/span> NULL<\/span>,\r\n    [Title] NVARCHAR<\/span>(160<\/span>)  NOT<\/span> NULL<\/span>,\r\n    [ArtistId] INTEGER<\/span>  NOT<\/span> NULL<\/span>,\r\n    FOREIGN<\/span> KEY<\/span> ([ArtistId]) REFERENCES<\/span> \"artists\"<\/span> ([ArtistId])\r\n                ON<\/span> DELETE<\/span> NO<\/span> ACTION<\/span> ON<\/span> UPDATE<\/span> NO<\/span> ACTION<\/span>\r\n)        \r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this tutorial, you have learned how to show the structure of a table in SQLite via a command-line shell program or SQL statement.<\/p>\r\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t