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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.