Home » SQL & PL/SQL » SQL & PL/SQL » How to see the structure of a table apart from "desc table_name"?
|
|
|
|
|
|
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 |
|
ramoradba
Messages: 2457 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 #429896 is a reply to message #429891] |
Fri, 06 November 2009 03:34 |
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
|
|
|
Goto Forum:
Current Time: Sat Nov 02 12:35:04 CDT 2024
|