Home » SQL & PL/SQL » SQL & PL/SQL » How to see the structure of a table apart from "desc table_name"?
How to see the structure of a table apart from "desc table_name"? [message #429775] Thu, 05 November 2009 14:19 Go to next message
pretty_baby1984@yahoo.com
Messages: 7
Registered: November 2009
Junior Member
Hi
I am just wondering,is there any other way to view the structure of a table apart from
"desc table_name" ?
Any sugegstion will be appreciated.

--
Thanks & Regards
Sujatha
Re: How to see the structure of a table apart from "desc table_name"? [message #429777 is a reply to message #429775] Thu, 05 November 2009 14:24 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are data dictionary views.

For example user_tables and user_tab_columns.

You can list them all with

select * from dictionary;



Re: How to see the structure of a table apart from "desc table_name"? [message #429778 is a reply to message #429775] Thu, 05 November 2009 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_tables, user_tab_columns, user_segments, user_tab_partitions...

Regards
Michel
Re: How to see the structure of a table apart from "desc table_name"? [message #429779 is a reply to message #429775] Thu, 05 November 2009 14:29 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel Sir and ThomasG Sir has already provided you the answer.

Adding a little bit of info here..

DESC MY_TABLE

is equivalent to
SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

[Updated on: Thu, 05 November 2009 14:33]

Report message to a moderator

Re: How to see the structure of a table apart from "desc table_name"? [message #429846 is a reply to message #429779] Fri, 06 November 2009 00:01 Go to previous messageGo to next message
pretty_baby1984@yahoo.com
Messages: 7
Registered: November 2009
Junior Member
Thank you all for the responses, I have tried to use the user_tab_columns with the specific table name, but it is retrieving more than one row for a particular table and the result is confusing.

I am glad if you can provide me the example with the result
for "Employees" table from "hr" schema to understand it better.

--
Thanks & Regards
Sujatha Nulu
Re: How to see the structure of a table apart from "desc table_name"? [message #429850 is a reply to message #429846] Fri, 06 November 2009 00:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you probably have more than one column in these tables, it sounds correct that you retrieve more than a single row from your query.
Re: How to see the structure of a table apart from "desc table_name"? [message #429872 is a reply to message #429850] Fri, 06 November 2009 01:17 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:

I am glad if you can provide me the example with the result
for "Employees" table from "hr" schema to understand it better.



SQL> ED
Wrote file afiedt.buf

  1  SELECT CNAME "Name",
  2  DECODE(NULLS,'NULL',NULL,NULLS) "Null?",
  3  DECODE(COLTYPE,
  4  'VARCHAR2',concat(concat(concat(COLtype,'('),WIDTH),')'),
  5  'DATE',COLTYPE,concat(concat(concat(COLtype,'('),PRECISION),DECODE(SCALE,0,')',CONCAT(',',SCALE)||')'))) "Type"
  6  FROM COL --its not advisable
 7* WHERE TNAME='EMPLOYEES'
SQL> /

Name                           Null?               Type
------------------------------ ------------------- -------------------------------------------------------------------
------------------------------------------------------------------------------------------
EMPLOYEE_ID                    NOT NULL            NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
LAST_NAME                      NOT NULL            VARCHAR2(25)
EMAIL                          NOT NULL            VARCHAR2(25)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                      NOT NULL            DATE
JOB_ID                         NOT NULL            VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)

11 rows selected.

SQL> SET LINE 80
SQL> DESC EMPLOYEES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> SHO USER
USER is "HR"
SQL> SELECT
  2  column_name "Name",
  3  DECODE(nullable,'N','NOT NULL',NULL) "Null?",
  4  concat(concat(concat(data_type,'('),data_length),')') "Type"
  5  FROM user_tab_columns
  6  WHERE table_name='EMPLOYEES';

Name                           Null?    Type
------------------------------ -------- -----------------------------------------------------
--------------------------------------
EMPLOYEE_ID                    NOT NULL NUMBER(22)
FIRST_NAME                              VARCHAR2(20)
LAST_NAME                      NOT NULL VARCHAR2(25)
EMAIL                          NOT NULL VARCHAR2(25)
PHONE_NUMBER                            VARCHAR2(20)
HIRE_DATE                      NOT NULL DATE(7)
JOB_ID                         NOT NULL VARCHAR2(10)
SALARY                                  NUMBER(22)
COMMISSION_PCT                          NUMBER(22)
MANAGER_ID                              NUMBER(22)
DEPARTMENT_ID                           NUMBER(22)

11 rows selected.

SQL>
SQL>
Re: How to see the structure of a table apart from "desc table_name"? [message #429886 is a reply to message #429872] Fri, 06 November 2009 02:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:

SALARY NUMBER(22)
COMMISSION_PCT NUMBER(22)
MANAGER_ID NUMBER(22)
DEPARTMENT_ID NUMBER(22)


COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


why these fields dont match??
Re: How to see the structure of a table apart from "desc table_name"? [message #429887 is a reply to message #429886] Fri, 06 November 2009 02:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Because for numbers you should not use data_length

[Edit: As was, by the way, indicated by the first query in the quoted post]

[Updated on: Fri, 06 November 2009 02:53]

Report message to a moderator

Re: How to see the structure of a table apart from "desc table_name"? [message #429891 is a reply to message #429886] Fri, 06 November 2009 03:14 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
ayush_anand wrote on Fri, 06 November 2009 02:37
Quote:

SALARY NUMBER(22)
COMMISSION_PCT NUMBER(22)
MANAGER_ID NUMBER(22)
DEPARTMENT_ID NUMBER(22)


COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


why these fields dont match??


See my post once again
sriram.
Re: How to see the structure of a table apart from "desc table_name"? [message #429896 is a reply to message #429891] Fri, 06 November 2009 03:34 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You need to modify the sql using DATA_PRECISION,DATA_SCALE field
You will need to use decode for Number and timestamp data type.

column column_name format a30
column data_type format a9
column data_length format a11
column nullable format a9
column column_name heading "Column Name"
column data_type   heading "Data|Type"
column data_length heading "Data|Length"
column nullable    heading "Null?"


select column_name,data_type,substr(decode( data_type, 'NUMBER',
decode( data_precision, NULL, NULL,'('||data_precision||','||data_scale||')' ),data_length),1,11) 
        data_length, decode( nullable, 'Y', 'null', 'not null' ) nullable 
from all_tab_columns 
where table_name = upper('&TABLE_NAME') order by column_name

[Updated on: Fri, 06 November 2009 03:39]

Report message to a moderator

Previous Topic: LEFT JOIN PROBLEM
Next Topic: updating table banktransactions with pl-sql
Goto Forum:
  


Current Time: Sat Sep 24 21:27:13 CDT 2016

Total time taken to generate the page: 0.09008 seconds