Oracle View all tables and fields

Oracle table Design

http://blog.csdn.net/lanpy88/article/details/7580820

Oracle View all tables and fields

Get table:

Select table_name from User_tables;//table for current user

Select table_name from All_tables;//table for all users

Select table_name from dba_tables;//Includes system table

Select table_name from dba_tables where owner= ' user name '

User_tables:

Table_name,tablespace_name,last_analyzed, etc.

Dba_tables:

Ower,table_name,tablespace_name,last_analyzed, etc.

All_tables:

Ower,table_name,tablespace_name,last_analyzed, etc.

All_objects:

Ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status, etc.

Get table field:

SELECT * from user_tab_columns where table_name= ' user table ';

SELECT * from all_tab_columns where table_name= ' user table ';

SELECT * from dba_tab_columns where table_name= ' user table ';

User_tab_columns:

TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

All_tab_columns:

OWER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

Dba_tab_columns:

OWER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID, etc.

Get Table Comments:

SELECT * FROM user_tab_comments

User_tab_comments:table_name,table_type,comments

The corresponding dba_tab_comments,all_tab_comments, the two more than the User_tab_comments ower column.

Get field Comments:

SELECT * FROM user_col_comments

User_col_comments:table_name,column_name,comments

The corresponding dba_col_comments,all_col_comments, the two more than the User_col_comments ower column.

For example:

1. View all the fields in the table EMP under the table user scott:

Select column_name from User_tab_columns t where t.table_name= 'EMP '; Note: The table name should be capitalized

///==========================================

Come from:http://28680360-qq-com.iteye.com/?show_full=true

///==========================================

Oracle View all tables and fields