MYSQL ORACLE 查询表名及字段--封装代码自动生成时用

1. mysql查询所有表、视图
SELECT
table_name as tablename,
table_comment as tablecomment
FROM
information_schema.TABLES

2. mysql查询列信息
SELECT
mysql' dialect,
TABLE_NAME AS tablename,
COLUMN_NAME AS columnname,
DATA_TYPE AS datatype,
CHARACTER_MAXIMUM_LENGTH AS cml,
COLUMN_COMMENT AS columncomment,
CASE COLUMN_KEY WHEN 'PRI' THEN 1 WHEN 'MUL' THEN 2 END AScolumnkey
FROM
information_schema.COLUMNS

3. ORACLE查询所有表、视图 
select 
table_name as "tablename",
table_type as "tabletype",
comments as "tablecomment" 
from user_tab_comments

4. ORACLE查询表的列信息
SELECT
    'oracle' dialect,
    utc.table_name AS tablename,
    utc.column_name AS columnname,
    utc.data_type AS datatype,
    utc.data_length AS cml,
    ucc.comments AS columncomment,
        CASE ucon.constraint_type
            WHEN 'P'   THEN '1'
            WHEN 'R'   THEN '2'
            ELSE ''
        END
    AS columnkey
FROM
    user_tab_columns utc
    LEFT JOIN user_col_comments ucc ON
        ucc.column_name = utc.column_name
    AND
        ucc.table_name = utc.table_name
    LEFT JOIN (
        SELECT
            cu.*,
            au.constraint_type
        FROM
            user_cons_columns cu,
            user_constraints au
        WHERE
                cu.constraint_name = au.constraint_name
            AND
                au.constraint_type IN (
                    'P','R'
                )
    ) ucon ON
        ucon.table_name = utc.table_name
    AND
        ucon.column_name = utc.column_name
WHERE
    utc.table_name = 'TABLENAME'