Oracle: View All Tables and table columns of the current user, including yellowcong and oracleyellowcong.

Oracle: View All Tables and table columns of the current user, including yellowcong and oracleyellowcong.

I have previously written a tool to dynamically generate the oracle entity class function, but this tool can only be used for the Searsar project. Therefore, it is of little significance, but it can also be extended to other entity classes. USER_COL_COMMENTS and USER_TAB_COLUMNS contain table fields and column information.

View All Tables of the current user

-- OWNER current USER name -- table_name table name SELECT * FROM DBA_TABLES where owner = 'jhyg10 _ old' and table_name like '% USER % ';

Query all tables with user names

View the columns of a table

View the column information in a table. USER_COL_COMMENTS contains the column information of the User table.

select * FROM USER_COL_COMMENTS COL where  COL.TABLE_NAME = 'CM_USER'

View the specific information of a table column

After no field information is obtained for each table, you need to obtain the specific information by combining the USER_TAB_COLUMNS table. This table contains information about each field.

Select col. COLUMN_NAME, COL. COMMENTS, TAB. DATA_TYPE, TAB. DATA_LENGTH FROM USER_COL_COMMENTS COL, USER_TAB_COLUMNS tab where col. TABLE_NAME = 'cm _ user' -- table and tab of CM_USER. TABLE_NAME = COL. TABLE_NAME and tab. COLUMN_NAME = COL. COLUMN_NAME

Optimized Field Information

In Oracle, the size of field information may be different from what we want. For example, if data of the Number (1, 2) type is a decimal type, however, we can directly look at the data of the int type, which requires attention. So I optimized the query method and made judgments for different types.

SELECT COL.COLUMN_NAME,       COL.COMMENTS,       case TAB.DATA_TYPE         when 'NUMBER' then          decode(sign(NVL(TAB.DATA_SCALE, 0)), 0, 'NUMBER', 'FLOAT')         else          TAB.DATA_TYPE       end as DATA_TYPE,       DECODE(TAB.DATA_TYPE,              'NUMBER',              TAB.DATA_PRECISION + TAB.DATA_SCALE,              TAB.DATA_LENGTH) AS DATA_LENGTH  FROM USER_COL_COMMENTS COL, USER_TAB_COLUMNS TAB WHERE COL.TABLE_NAME = 'CMST_USER'   AND TAB.TABLE_NAME = COL.TABLE_NAME   AND TAB.COLUMN_NAME = COL.COLUMN_NAME

The optimized query results are similar to the above changes because there is no data of the Number type. If yes, it will naturally change.

The following shows a data with Number type. You can find that the size and type of the optimized data can be correct. The FLOAT type is self-added, mainly to generate code directly, it can distinguish between integers and decimals.