Show the structure of the table in SQL

Question

Can someone explain things a little better to me? How do I show the structure of a table? I run the select * from table; and of course it displays all that's in the table. But, I am being asked to show the structure of the table. What does that mean, and what is the command?

Here's my table below.

SQL> select * from dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

 SQL>

score 22 · Answer 1 · edited Mar 18 '16 at 05:58

22

Try this out: describe table_name

edited Mar 18 '16 at 05:58

ajshort

3,684
5
29
43

answered Mar 18 '16 at 05:40

sribin

221
2
2

Or `desc table_name` but It works with Oracle and not with MSSQL. – Bruno L. Sep 19 '21 at 08:38

score 19 · Accepted Answer · edited May 23 '17 at 12:01

19

To list columns and data types, I typically use

SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='your_table_name';

It's been a while since I've worked with Oracle though. ALL_TAB_COLUMNS might actually be ALL_TAB_COLS.

If you need to display the full CREATE TABLE statement, see How to get Oracle create table statement in SQL*Plus

edited May 23 '17 at 12:01

Community

1
1

answered Sep 09 '11 at 01:51

Michael Berkowski

267,341
46
444
390

1

Its just not clicking for me. – user770022 Sep 09 '11 at 02:07
@user770022 Yes, I believe `DESCRIBE` is supported in Oracle as well. – Michael Berkowski Sep 09 '11 at 02:37

score 1 · Answer 3 · answered Aug 20 '20 at 05:43

1

You can use sqlplus command describe <SCHEMA_OWNER.TABLE>

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12019.htm

answered Aug 20 '20 at 05:43

27P

1,183
16
22

Show the structure of the table in SQL

3 Answers3

Linked