Home » Other » General » How can I get the info on tables and columns and their data types. (Oracle 10g, windows xp)
How can I get the info on tables and columns and their data types. [message #404466] Thu, 21 May 2009 21:05 Go to next message
kang
Messages: 89
Registered: November 2007
Member
How can I get the info on tables and columns and their data types.
tables name, column name, data type, comments
Re: How can I get the info on tables and columns and their data types. [message #404467 is a reply to message #404466] Thu, 21 May 2009 21:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc all_tab_columns
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 COLUMN_NAME				   NOT NULL VARCHAR2(30)
 DATA_TYPE					    VARCHAR2(106)
 DATA_TYPE_MOD					    VARCHAR2(3)
 DATA_TYPE_OWNER				    VARCHAR2(30)
 DATA_LENGTH				   NOT NULL NUMBER
 DATA_PRECISION 				    NUMBER
 DATA_SCALE					    NUMBER
 NULLABLE					    VARCHAR2(1)
 COLUMN_ID					    NUMBER
 DEFAULT_LENGTH 				    NUMBER
 DATA_DEFAULT					    LONG
 NUM_DISTINCT					    NUMBER
 LOW_VALUE					    RAW(32)
 HIGH_VALUE					    RAW(32)
 DENSITY					    NUMBER
 NUM_NULLS					    NUMBER
 NUM_BUCKETS					    NUMBER
 LAST_ANALYZED					    DATE
 SAMPLE_SIZE					    NUMBER
 CHARACTER_SET_NAME				    VARCHAR2(44)
 CHAR_COL_DECL_LENGTH				    NUMBER
 GLOBAL_STATS					    VARCHAR2(3)
 USER_STATS					    VARCHAR2(3)
 AVG_COL_LEN					    NUMBER
 CHAR_LENGTH					    NUMBER
 CHAR_USED					    VARCHAR2(1)
 V80_FMT_IMAGE					    VARCHAR2(3)
 DATA_UPGRADED					    VARCHAR2(3)
 HISTOGRAM					    VARCHAR2(15)


Oracle has many views to answer questions similar to what you asked.
These views begin with
DBA_
ALL_
USER_
where DBA_ list everything in DB, ALL_ list everything you have SELECT privs against, & USER_ everything owned by your schema.

To see what might hold the answer you desire about COLUMNS do:
SELECT VIEW_NAME FROM DBA_VIEW WHERE VIEW_NAME LIKE '%COLUMN%';

To see what might hold the answer you desire about COMMENT do:
SELECT VIEW_NAME FROM DBA_VIEW WHERE VIEW_NAME LIKE '%COMMENT%';


[Updated on: Thu, 21 May 2009 21:20]

Report message to a moderator

Re: How can I get the info on tables and columns and their data types. [message #404518 is a reply to message #404466] Fri, 22 May 2009 01:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To add to BlackSwan's answer, Oracle also has a nice view named DICT (or DICTIONARY)
SQL> desc dict 
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                                VARCHAR2(30)
 COMMENTS                                  VARCHAR2(4000)

They list the name of each catalog views with a comment on their usage.

Oracle also has a nice Reference book that lists and explains almost all of them.

Regards
Michel

[Updated on: Fri, 22 May 2009 01:32]

Report message to a moderator

Previous Topic: EMP TABLE IN ORACLE 10G
Next Topic: Deployment - Best Practices
Goto Forum:
  


Current Time: Thu Mar 28 16:55:50 CDT 2024