In Oracle, how does one obtain user table information and other details ?, Oracle acquisition

In Oracle, how does one obtain user table information and other details ?, Oracle acquisition

1. Get the User Name of the current user

SELECT USERNAME FROM USER_USERS;

2. Obtain the names of all tables under a user.

SELECT TABLE_NAME FROM ALL_TABLES where owner = 'username'; -- case sensitive

3. Obtain the details of a table under the current user.

Select t. TABLE_NAME, -- table name T. COLUMN_NAME, -- field name T. DATA_TYPE, -- field type T. DATA_LENGTH, -- length T. NULLABLE -- whether it is null FROM ALL_TAB_COLS TWHERE TABLE_NAME = 'table name'; -- case sensitive

4. Obtain information about all tables and fields of the current user.

(1)

SELECT T1.TABLE _ NAME, -- table English NAME T6.COMMENTS, -- table chinese NAME T1.COLUMN _ ID, -- field no. T1.COLUMN _ NAME, -- field English NAME T5.COMMENTS, -- field Chinese NAME T1.DATA _ TYPE, -- field type T1.DATA _ LENGTH, -- Data LENGTH T1.CHAR _ LENGTH, -- character LENGTH T1.DATA _ PRECISION, -- value LENGTH T1.DATA _ SCALE, -- numerical PRECISION T1.NULLABLE, -- whether to allow null values T4.INDEX _ NAME, -- index NAME T4.COLUMN _ POSITION, -- index Field Sequence Number T4.DESCEND -- index field sorting method FROM USER_TAB_COLUMNS T1 -- table Field List left join (SELECT T2.TABLE _ NAME, -- table NAME T2.COLUMN _ NAME, -- field NAME T2.COLUMN _ POSITION, -- Field Sequence Number T2.DESCEND, -- sorting method T3.INDEX _ NAME -- index name from USER_IND_COLUMNS T2 -- index field left join USER_INDEXES T3 -- index information ON T2.TABLE _ NAME = T3.TABLE _ name and T2.INDEX _ NAME = T3.INDEX _ name and T3.STATUS = 'valid' AND T3.UNIQUENESS = 'unique ') t4 -- UNIQUE: unique index ON T1.TABLE _ NAME = T4.TABLE _ name and T1.COLUMN _ NAME = T4.COLUMN _ name left join USER_COL_COMMENTS T5 ON T1.TABLE _ NAME = T5.TABLE _ name and T1.COLUMN _ NAME = T5.COLUMN _ NAME LEFT JOIN USER_TAB_COMMENTS T6 ON T1.TABLE _ NAME = T6.TABLE _ nameorder by T1.TABLE _ NAME, t1.COLUMN _ ID;

(2)

SELECT T1.OWNER, -- table Schema T1.TABLE _ NAME, -- table English NAME T6.COMMENTS, -- table chinese NAME T1.COLUMN _ ID, -- field no. T1.COLUMN _ NAME, -- field English NAME T5.COMMENTS, -- field Chinese name T1.DATA _ TYPE, -- field TYPE T1.DATA _ LENGTH, -- Data LENGTH T1.CHAR _ LENGTH, -- character LENGTH T1.DATA _ PRECISION, -- value LENGTH T1.DATA _ SCALE, -- numerical PRECISION T1.NULLABLE, -- whether to allow null values T4.INDEX _ NAME, -- index NAME T4.COLUMN _ POSITION, -- index Field Sequence Number T4.DESCEND -- index field sorting method FROM ALL_TAB_COLUMNS T1 -- table Field List left join (SELECT T2.TABLE _ OWNER, -- table T2.TABLE _ NAME, -- table NAME T2.COLUMN _ NAME, -- field NAME T2.COLUMN _ POSITION, -- Field Sequence Number T2.DESCEND, -- sorting method T3.INDEX _ NAME -- index name from ALL_IND_COLUMNS T2 -- index field left join ALL_INDEXES T3 -- index information ON T2.TABLE _ OWNER = T3.TABLE _ owner and T2.TABLE _ NAME = T3.TABLE _ name and T2.INDEX _ NAME = T3.INDEX _ name and T3.STATUS = 'valid' AND T3.UNIQUENESS = 'unique ') t4 -- UNIQUE: unique index ON T1.OWNER = T4.TABLE _ owner and T1.TABLE _ NAME = T4.TABLE _ name and T1.COLUMN _ NAME = T4.COLUMN _ name left join ALL_COL_COMMENTS T5 ON T1.OWNER = T5.OWNER AND T1.TABLE _ NAME = T5.TABLE _ NAME AND T1.COLUMN _ NAME = T5.COLUMN _ name left join ALL_TAB_COMMENTS T6 ON T1.OWNER = T6.OWNER AND T1.TABLE _ NAME = T6.TABLE _ NAMEWHERE T1.OWNER IN ('username ') order by T1.OWNER, T1.TABLE _ NAME, T1.COLUMN _ ID;

Note: The difference between method (1) and method (2) is that the table used is different. The table starts with USER, queries the current USER, starts with ALL, queries ALL users, and starts with DBA, query the contents of the system table. In daily use, you can flexibly select different tables to query the content you need.