你的位置:首页 > 数据库

[数据库]sql:Oracle11g 表,视图,存储过程结构查询


--GetTablesSELECT owner, object_name, created FROM all_objects WHERE (owner in ( select USERNAME from user_users )) AND object_type = 'TABLE'   ORDER BY owner,  object_name;--GetTableColumns--declare @owner varchar(200),@tablename varchar(200)select * from all_tab_columns;select cols.column_name,               cols.data_type,               cols.data_length,               cols.data_precision,               cols.data_scale,               cols.nullable,                   cmts.comments,               cols.owner,               cmts.owner,               cols.table_name           from all_tab_columns cols,               all_col_comments cmts            where cols.owner = cmts.owner             and cols.table_name = cmts.table_name             and cols.column_name = cmts.column_name            --and ROWNUM <= 10            order by column_id;                    --表结构              select cols.column_name,               cols.data_type,               cols.data_length,               cols.data_precision,               cols.data_scale,               cols.nullable,                   cmts.comments           from all_tab_columns cols,               all_col_comments cmts            where               cols.owner = 'GEOVIN' --            and cols.table_name = 'EMPLOYEELIST'--            and cols.owner = cmts.owner             and cols.table_name = cmts.table_name             and cols.column_name = cmts.column_name            order by column_id; --GetViewsselect v.owner, v.view_name, o.created        from all_views  v,          all_objects o         where v.view_name = o.object_name         and o.object_type = 'VIEW'         and (v.owner in ( select USERNAME from user_users ))        order by v.owner, v.view_name;---GetViewColumnsselect cols.column_name,               cols.data_type,               cols.data_length,               cols.data_precision,               cols.data_scale,               cols.nullable,                   cmts.comments           from all_tab_columns cols,               all_col_comments cmts            where               cols.owner = 'GEOVIN' --            and cols.table_name = 'v_EMPLOYEELIST'---            and cols.owner = cmts.owner             and cols.table_name = cmts.table_name             and cols.column_name = cmts.column_name            order by column_id; ----GetTablePrimaryKey select             cols.constraint_name,             cols.column_name,             cols.position           from            all_constraints   cons,            all_cons_columns  cols          where             cons.OWNER = 'GEOVIN'            and cons.table_name = 'EMPLOYEELIST'            and cons.constraint_type='P'            and cols.owner = cons.owner            and cols.table_name = cons.table_name              and cols.constraint_name = cons.constraint_name           order by cons.constraint_name, cols.position; ---GetTableIndexes select    idx.owner, idx.uniqueness, con.constraint_type, idx.table_type, col.*        from    all_ind_columns col,              all_indexes idx,              all_constraints con        where    idx.table_owner = '{0}'              AND idx.table_name = '{1}'              AND idx.owner = col.index_owner              AND idx.index_name = col.index_name              AND idx.owner = con.owner (+)              AND idx.table_name = con.table_name(+)              AND idx.index_name = con.constraint_name(+); ---GetTableKeys select           cols.constraint_name,           cols.column_name,           cols.position,           r_cons.table_name related_table_name,           r_cols.column_name related_column_name         from          all_constraints   cons,          all_cons_columns  cols,          all_constraints   r_cons,          all_cons_columns  r_cols        where cons.OWNER = 'GEOVIN'         and cons.table_name = 'EMPLOYEELIST'         and cons.constraint_type='R'         and cols.owner = cons.owner         and cols.table_name = cons.table_name           and cols.constraint_name = cons.constraint_name          and r_cols.owner = cons.r_owner          and r_cols.constraint_name = cons.r_constraint_name          and r_cons.owner = r_cols.owner          and r_cons.table_name = r_cols.table_name          and r_cons.constraint_name = r_cols.constraint_name         order by cons.constraint_name, cols.position; ---GetViewText select    text        from    all_views        where    owner = 'GEOVIN'              and view_name = 'v_EMPLOYEELIST'; --GetCommands select methods.owner,               methods.package_name,               methods.object_name,               methods.overload,              ao.object_type,              ao.created,              ao.status,              ao.object_id            from            (select distinct owner, package_name, object_name, overload, object_id from ALL_ARGUMENTS               where (owner in ( select USERNAME from user_users ))              ) methods,              all_objects ao            where ao.object_id = methods.object_id              order by methods.owner, methods.package_name, methods.object_name; ---GetCommandParameters select             ARGUMENT_NAME,             POSITION,             SEQUENCE,             DATA_LEVEL,             DATA_TYPE,             IN_OUT,             DATA_LENGTH,             DATA_PRECISION,             DATA_SCALE           from ALL_ARGUMENTS           where object_ID=0          and object_name = '{1}'          and 2          order by position; ---GetCommandText