Show the structure of the table in SQL

sql sqlplus

user770022 · Sep 9, 2011 · Viewed 54.2k times · Source

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>

Answer

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

Show the structure of the table in SQL

Answer

Related questions