Sample Code of several methods of viewing Table structure in Oracle

1Band description command

The method of use is as follows:

SQL > describe nchar_tst (nchar_tst is the table name)

The results displayed are as follows:

Is the name empty? Type-NAME NCHAR (6) ADDR NVARCHAR2 (16) SAL NUMBER (9)

2. DBMSrecording METADATA.GETRANDDL package

The method of use is as follows:

SQL > SELECT DBMS_METADATA.GET_DDL ('TABLE','NCHAR_TST') FROM DUAL

If the result displayed is incomplete, it is as follows:

CREATE TABLE "SCOTT". "NCHAR_TST" ("NAME" NCHAR (6), "ADDR" NVARCHAR2 (16)

Then modify it as follows:

SQL > SET LONG 9999 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 PCTINCREASE 2) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255LOGGING STORAGE (INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

3. Methods:

Through the user_tab_cols, user_col_comments, user_constraints, user_cons_ columns table federated query in Oracle.

User_tab_cols is used to get the column information of the corresponding user table

User_col_comments is used to get the comment information of the corresponding user table column.

The constraints used by user_constraints to get the user table

The user can access the column in the user_cons_columns constraint.

Sample code:

Select t.tableroomnamereparenti.columnroomnamerecovert.dataroomtyperet.dataaccountretyperet.nullablenotet.columnaccounidjournal c.comments, (SELECT CASE WHEN t.column_name=m.column_name THEN 1 ELSE 0 END FROM DUAL) iskey FROM user_tab_cols t, user_col_comments c, (select m.column_name from user_constraints s) User_cons_columns m where lower (m.table_name) = 'us_cities' and m.table_name=s.table_name and m.constraint_name=s.constraint_name and s. Intact typewritten materials P') m WHERE lower (t.table_name) =' us_cities' and c.table_name=t.table_name and c.column_name=t.column_name and t.hiddendum columnflowers no 'order by t.column_id

Summary

The above is the sample code of several ways to view the table structure of Oracle introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply you in time. Thank you very much for your support to the website!