Oracle desc table

desc.sql


!cat desc1.sql

spool /tmp/&&1.txt
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SELECT 'SELECT ' FROM DUAL;
select chr(9)||lower(column_name)||',' from USER_tab_columns
where table_Name = UPPER('&&1');
SELECT ' FROM &1 WHERE ROWNUM <2' FROM DUAL;
SET HEADING ON
UNDEFINE 1
SET VERIFY ON
SET FEEDBACK ON
SPOOL OFF









desc.sql


SET PAUSE ON
SET PAUSE 'Any Key...;'
SET PAGES 24
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
break on tab_own skip 1 on tab_name skip 1
spool tab_desc.lst
select
owner Tab_Own
,table_name Tab_Name
,column_name Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
,'NUMBER','('
||decode(to_char(data_precision)
,null,'38'
, to_char(data_precision)||
decode(data_scale,null,''
, ','||data_scale)
)
||')'
,'DATE',null
,'LONG',null
,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
) col_def
from dba_tab_columns
where table_name like upper (nvl('EMP','ANUJ')||'%')
order by 1,2,column_id,3,4
/
spool off
clear col








SQL> @desc emp
Any Key...;


TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
ANUJ EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)


HR EMPLOYEES EMPLOYEE_ID Y NUMBER(6,0)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
EMAIL Y VARCHAR2(25)
PHONE_NUMBER N VARCHAR2(20)
HIRE_DATE Y DATE
JOB_ID Y VARCHAR2(10)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
Any Key...;

TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------

HR EMP_DETAILS_VIEW EMPLOYEE_ID Y NUMBER(6,0)
JOB_ID Y VARCHAR2(10)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
LOCATION_ID N NUMBER(4,0)
COUNTRY_ID N CHAR(2)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
DEPARTMENT_NAME Y VARCHAR2(30)
JOB_TITLE Y VARCHAR2(35)
CITY Y VARCHAR2(30)
STATE_PROVINCE N VARCHAR2(25)
COUNTRY_NAME N VARCHAR2(40)
REGION_NAME N VARCHAR2(25)


SCOTT EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
Any Key...;

TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
SCOTT EMP JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)


SYS EMP EMPNO N NUMBER(38)



44 rows selected.