Several ways Oracle views the table structure

1,describe command
Here's how to use it:
Sql> describe NCHAR_TST (nchar_tst as table name)
The results shown are as follows:
is the name empty? Type
----------------------------------------- -------- ----------------------------
NAME NCHAR (6)
ADDR NVARCHAR2 (16)
SAL Number (9,2)
2,dbms_metadata. GET_DDL Bag
Here's how to use it:

[SQL]View PlainCopy

1. Sql> SELECT dbms_metadata.  GET_DDL (' TABLE ',' nchar_tst ') from DUAL;

If the displayed results are not complete, as follows:
CREATE TABLE "SCOTT". " Nchar_tst "
("NAME" NCHAR (6),
"ADDR" NVARCHAR2 (16

Then modify the following:

[SQL]View PlainCopy

1. Sql> SET LONG 9999
2. Sql> SELECT dbms_metadata.  GET_DDL (' TABLE ',' nchar_tst ') from DUAL;

The following results are displayed:

Dbms_metadata. GET_DDL (' TABLE ', ' nchar_tst ')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT". " Nchar_tst "
("NAME" NCHAR (6),
"ADDR" NVARCHAR2 (16),
"SAL" Number (9,2)
) PCTFREE pctused Initrans 1 Maxtrans 255 LOGGING
STORAGE (INITIAL 12288 NEXT 12288 minextents 1 MAXEXTENTS 249 pctincrease 50
Freelists 1 FREELIST GROUPS 1 buffer_pool DEFAULT) tablespace "SYSTEM"
3, Method:
Federated queries through User_tab_cols, user_col_comments, user_constraints, user_cons_columns tables in Oracle.
User_tab_cols is used to obtain the column information of the corresponding user table;
User_col_comments is used to obtain the corresponding User table column annotation information;
User_constraints is used to obtain the constraints of the user table;
User_cons_columns a user-accessible column in a constraint.


Example code:

[SQL]View PlainCopy

2. Select T.table_name,t.column_name,t.data_type,t.data_length,t.nullable,t.column_id,c.comments,
4. (SELECT case is t.column_name=m.column_name then 1 ELSE 0 END from DUAL) IsKey
6. From User_tab_cols T, user_col_comments C, (select M.column_name from user_constraints s, user_cons_columns M
8. where lower (m.table_name) =' us_cities ' and m.table_name=s.table_name
10. and M.constraint_name=s.constraint_name and s.constraint_type=' P ') m
12. WHERE lower (t.table_name) =' us_cities '
14. and C.table_name=t.table_name
16. and C.column_name=t.column_name
18. and t.hidden_column=' NO '
20. ORDER BY t.column_id
    

Several ways Oracle views the table structure