oracle获得数据库字段明细的脚本

2015-09-04 16:49:04 访问(1580) 赞(0) 踩(0)

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