视图showdc查看字段的名称和注释

create or replace view showdc as
select
       TableName,
       TableComment,
       DataColumnName,
       DataColumnComment,
       DataColumnType,
       SqlLength,
       SqlSize,
       SqlSizeEnd,
       AllowDBNull,
       SqlDefaultValue,
       ForeignKeyTableName,
       ForeignKeyDataColumnName,
       ForeignKeyTableUser,
       DataColumnIsPrimaryKey,
       IsUnique
       from
       (
          select
            one.TABLE_NAME as tableId,
            one.TABLE_NAME as tableName,
            '' as tableComment,
            one.COLUMN_ID as DataColumnIndex,
            one.COLUMN_ID as DataColumnId,
            one.column_name as DataColumnName,
            two.comments as DataColumnComment,
            (
              case when
                exists(
                select
                1
                from
                user_cons_columns three,
                user_constraints four
                where
                three.CONSTRAINT_NAME = four.CONSTRAINT_NAME
                and four.CONSTRAINT_TYPE = 'P'
                and three.TABLE_NAME = four.TABLE_NAME
                and one.TABLE_NAME = three.TABLE_NAME
                and one.COLUMN_NAME = three.COLUMN_NAME
              ) then 1 else 0 end
            ) as DataColumnIsPrimaryKey,
            one.data_type as DataColumnType,
            one.data_length as SqlLength,
            one.data_precision as SqlSize,
            one.Data_Scale as SqlSizeEnd,
            (case when one.nullable = 'Y' then 1 else 0 end) as AllowDBNull,
            one.Data_default as SqlDefaultValue,
            (case when  five.constraint_name is null  then 0 else 1 end) as IsUnique,
            five.constraint_name as UniqueName,
            nine.R_TABLE_NAME as ForeignKeyTableId,
            nine.R_OWNER as ForeignKeyTableUser,
            nine.R_TABLE_NAME as ForeignKeyTableName,
            nine.R_CONSTRAINT_NAME as ForeignKeyDataColumnId,
            nine.R_COLUMN_NAME as ForeignKeyDataColumnName
        from
            user_tab_columns one,
            user_col_comments two,
            (
             select
                three.CONSTRAINT_NAME,
                three.TABLE_NAME,
                three.COLUMN_NAME
                from
                user_cons_columns three,
                user_constraints four
                where
                three.CONSTRAINT_NAME = four.CONSTRAINT_NAME
                and four.CONSTRAINT_TYPE = 'U'
                and three.TABLE_NAME = four.TABLE_NAME
            ) five,
            (
            select
                six.OWNER,
                six.CONSTRAINT_NAME,
                six.TABLE_NAME,
                six.COLUMN_NAME,
                seven.R_CONSTRAINT_NAME,
                eight.OWNER as R_OWNER,
                eight.TABLE_NAME as R_TABLE_NAME,
                eight.COLUMN_NAME as R_COLUMN_NAME
                from
                user_cons_columns six,
                user_constraints seven,
                user_cons_columns eight
                where
                six.CONSTRAINT_NAME = seven.CONSTRAINT_NAME
                and seven.CONSTRAINT_TYPE = 'R'
                and six.TABLE_NAME = seven.TABLE_NAME
                and eight.CONSTRAINT_NAME = seven.R_CONSTRAINT_NAME
                )nine
        where
            one.Table_Name = two.Table_Name
            and one.Column_Name = two.Column_Name
            and one.TABLE_NAME = five.table_name(+)
            and one.COLUMN_NAME = five.column_name(+)
            and one.TABLE_NAME = nine.TABLE_NAME(+)
            and one.COLUMN_NAME = nine.COLUMN_NAME(+)
        order by one.Table_Name asc, one.COLUMN_ID asc,one.Column_Name asc
      );