How to view table structure in SQL?

To show the table structure with all its column’s attributes: name, datatype, primary key, default value, etc.

In SQL Server, use sp_help function:

sp_help [ [ @objname = ] table_name ]

In MySQL and Oracle, you can use DESCRIBE:

DESCRIBE table_name;

Or

DESC table_name;

In PostgreSQL, here is the go-to statement:

SELECT
  *
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = table_name;

In SQLite, it’s as simple as this:

PRAGMA table_info(table_name);

Example

We have to table Customers in SQLite, and to view the table structure:

PRAGMA table_info(Customer);

Here is the results:

cid	name	type	notnull	dflt_value	pk
0	CustomerId	INTEGER	1		1
1	FirstName	NVARCHAR(40)	1		0
2	LastName	NVARCHAR(20)	1		0
3	Company	NVARCHAR(80)	0		0
4	Address	NVARCHAR(70)	0		0
5	City	NVARCHAR(40)	0		0
6	State	NVARCHAR(40)	0		0
7	Country	NVARCHAR(40)	0		0
8	PostalCode	NVARCHAR(10)	0		0
9	Phone	NVARCHAR(24)	0		0
10	Fax	NVARCHAR(24)	0		0
11	Email	NVARCHAR(60)	1		0
12	SupportRepId	INTEGER	0		0

In TablePlus, you can view the table structure by clicking on the structure button at the bottom of the window, or use the shortcut key Cmd + Shift + ]

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS