Home » SQL & PL/SQL » SQL & PL/SQL » How I get the Tables' source
How I get the Tables' source [message #105] Mon, 14 January 2002 19:24 Go to next message
koubai
Messages: 4
Registered: January 2002
Junior Member
I want to get the some Tables' sources.
I can use pl/sql to get the procedure's sources like
SELECT TEXT FROM SYS.USER_SOURCE WHERE NAME = 'XX_TABLE' ORDER BY
SYS.USER_SOURCE.LINE
But I can'T get the Tables' source by the same way.
How I get it?
Please give me the advice. Thank you
Re: How I get the Tables' source [message #108 is a reply to message #105] Tue, 15 January 2002 01:18 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
What u mean by table's source ? can u give an example.
Re: How I get the Tables' source(sample) [message #109 is a reply to message #108] Tue, 15 January 2002 01:27 Go to previous messageGo to next message
koubai
Messages: 4
Registered: January 2002
Junior Member
like
CREATE TABLE USR1.FLOCK
(
CDKEY VARCHAR2(1) NOT NULL,
CDKEY1 VARCHAR2(32) NOT NULL,
SUCNT NUMBER(3,0),
CDUSR VARCHAR2(64),
DTUPDATE DATE,
CONSTRAINT FLOCKPK PRIMARY KEY (CDKEY, CDKEY1)
)
/
COMMENT ON TABLE USR1.FLOCK IS 'TEST'
/
COMMENT ON COLUMN USR1.FLOCK.CDKEY IS 'CDKEY'
/
COMMENT ON COLUMN USR1.FLOCK.CDKEY1 IS 'CDKEY‚P'
/
COMMENT ON COLUMN USR1.FLOCK.DTUPDATE IS 'DTDATE'
/
Re: How I get the Tables' source(sample) [message #111 is a reply to message #108] Tue, 15 January 2002 01:44 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

These information is not stored in one place.you can get these information in third-party tool like 'TOAD'

here is an example how to get table defination(remember it does not say about constraint,index etc.)

set pages 0
set heading off
col ord noprint
col table_name noprint
col col_name format a120
SELECT TABLE_NAME,COLUMN_ID ORD,
DECODE(COLUMN_ID,1,'CREATE TABLE '||TABLE_NAME||
'(',',')|| COLUMN_NAME||' '||DATA_TYPE||DECODE(DATA_TYPE,'DATE','','LONG','',
'NUMBER',DECODE(DATA_PRECISION,
NULL,'','('||DATA_PRECISION||','||DATA_SCALE||')'),DECODE(DATA_LENGTH,0,'','('||
DATA_LENGTH||')'))||' '||DECODE(NULLABLE,'Y','','NOT NULL')COL_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME ='EMPLOYEES'--change Here
UNION
SELECT TABLE_NAME,99999,');'
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME ='EMPLOYEES'--change Here
ORDER BY 1,2,3 DESC

cheers
pratap
Delivering the top secret !!! Re: How I get the Tables' source [message #113 is a reply to message #105] Tue, 15 January 2002 01:53 Go to previous messageGo to next message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
Getting the DDL -

Program >Command Prompt
C:>exp userid=test/test owner=test
C:>imp userid=test/test full=Y indexfile=c:/test.sql
--You will get only table structure here
--To get index,view and trigger structure
C:>imp userid=test/test full=Y show=Y

open the test.sql and you will see the table structure of all the tables for test user

cya
Re: How I get the Tables' source(thanks) [message #123 is a reply to message #108] Tue, 15 January 2002 15:03 Go to previous messageGo to next message
koubai
Messages: 4
Registered: January 2002
Junior Member
I did it. Thank you very much.
Re: Delivering the top secret !!! Re: How I get the Tables' source [message #124 is a reply to message #113] Tue, 15 January 2002 15:21 Go to previous message
koubai
Messages: 4
Registered: January 2002
Junior Member
I did it too. It's really a easy way.
Thank you very much.
Previous Topic: Killing a user session
Next Topic: create file using utl_file
Goto Forum:
  


Current Time: Tue Mar 31 05:44:53 CDT 2020