Home » SQL & PL/SQL » Client Tools » script to describe tables (Oracle 10g, 10.2.0.4.0, Solaris 10)
script to describe tables [message #458787] Tue, 01 June 2010 19:35 Go to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Good day!

Pls help me how to create a script that will describe only the structure of 150 tables of a database.
I have it just like this, but I cannot produce the name of the table:

spool desctab.sql
select 'desc '||owner||'.'||table_name||'' from all_tables where owner='SKIMA'
order by table_name;
spool off

.. the output of the spool file is something like this:

Name Null? Type
----------------------------------------- -------- -------------
OFC_CODE VARCHAR2(3)
OFFICE VARCHAR2(35)
DESCRIPTION VARCHAR2(50)
REMARKS VARCHAR2(200)
OFFICER VARCHAR2(35)

Thanks in advance.
Re: script to describe tables [message #458788 is a reply to message #458787] Tue, 01 June 2010 19:41 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
QL> set term on echo on
SQL> @a.sql
SQL> spool desctab.sql
SQL> select 'desc '||table_name from user_tables order by table_name;

'DESC'||TABLE_NAME
-----------------------------------
desc COUNTRIES
desc DEPARTMENTS
desc EMP
desc EMPLOYEE
desc EMPLOYEES
desc JOBS
desc JOB_HISTORY
desc LOCATIONS
desc REGIONS
desc TESTER

10 rows selected.

SQL> spool off
SQL> set term on echo on
SQL> @desctab.sql
SQL> SQL> select 'desc '||table_name from user_tables order by table_name;
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SQL> 
SQL> 'DESC'||TABLE_NAME
SP2-0734: unknown command beginning "'DESC'||TA..." - rest of line ignored.
SQL> -----------------------------------
SQL> desc COUNTRIES
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID				   NOT NULL CHAR(2)
 COUNTRY_NAME					    VARCHAR2(40)
 REGION_ID					    NUMBER

SQL> desc DEPARTMENTS
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID				   NOT NULL NUMBER(4)
 DEPARTMENT_NAME			   NOT NULL VARCHAR2(30)
 MANAGER_ID					    NUMBER(6)
 LOCATION_ID					    NUMBER(4)

SQL> desc EMP
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 ENAME						    VARCHAR2(10)
 JOB						    VARCHAR2(9)
 MGR						    NUMBER(4)
 HIREDATE					    DATE
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 DEPTNO 					    NUMBER(2)

SQL> desc EMPLOYEE
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID 					    VARCHAR2(10)
 COMPANY_NAME					    VARCHAR2(50)

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> desc JOBS
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_ID 				   NOT NULL VARCHAR2(10)
 JOB_TITLE				   NOT NULL VARCHAR2(35)
 MIN_SALARY					    NUMBER(6)
 MAX_SALARY					    NUMBER(6)

SQL> desc JOB_HISTORY
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID				   NOT NULL NUMBER(6)
 START_DATE				   NOT NULL DATE
 END_DATE				   NOT NULL DATE
 JOB_ID 				   NOT NULL VARCHAR2(10)
 DEPARTMENT_ID					    NUMBER(4)

SQL> desc LOCATIONS
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOCATION_ID				   NOT NULL NUMBER(4)
 STREET_ADDRESS 				    VARCHAR2(40)
 POSTAL_CODE					    VARCHAR2(12)
 CITY					   NOT NULL VARCHAR2(30)
 STATE_PROVINCE 				    VARCHAR2(25)
 COUNTRY_ID					    CHAR(2)

SQL> desc REGIONS
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID				   NOT NULL NUMBER
 REGION_NAME					    VARCHAR2(25)

SQL> desc TESTER
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER

SQL> 
SQL> 10 rows selected.
SQL> 
SQL> SQL> spool off
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> 
Re: script to describe tables [message #458790 is a reply to message #458788] Tue, 01 June 2010 20:08 Go to previous messageGo to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Thank you for this..
but how about if I want a particular schema owner?
Re: script to describe tables [message #458791 is a reply to message #458790] Tue, 01 June 2010 20:12 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
>but how about if I want a particular schema owner?
login as that user or modify the posted SQL.

I just showed that it could be done & how it is done.
You are free to modify to meet your specific requirements.
Re: script to describe tables [message #458799 is a reply to message #458791] Tue, 01 June 2010 22:36 Go to previous message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Yeah, thanks very much for this! I just added the "set term on echo on" on the same script and it works!

Regards..
Previous Topic: Toad Material
Next Topic: DBMS_OUTPUT
Goto Forum:
  


Current Time: Wed Oct 23 11:01:40 CDT 2019