sql:Oracle11g 表,视图,存储过程结构查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235

-- Oracle 11 G --20160921 涂聚文再次修改 --Geovin Du --GetTables SELECT owner, object_name, created FROM all_objects WHERE (owner in ( select USERNAME from user_users  )) AND object_type = 'TABLE'  ORDER BY owner,    object_name;   ---GEOVIN SELECT owner, object_name, created FROM all_objects WHERE (owner in ( select USERNAME from user_users  )) AND object_type = 'TABLE' and owner='GEOVIN'  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  cols.owner= 'GEOVIN'                         --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;                                --GetViews select 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;                                                       ---GetViewColumns 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 = '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 = 'VIEW_BOOKADMINISTRATOR';                                                              --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 = 'PROCSELECTBOOKKINDLIST' --PROCSELECTBOOKKINDLIST                     --and 2                     order by position;    --                     select *  from ALL_ARGUMENTS                     where --object_ID=0                     --and                      object_name = 'PROCSELECTBOOKKINDLIST' --PROCSELECTBOOKKINDLIST                     --and 2                     order by position;                                                              ---GetCommandText 显示存储过程脚本  desc user_source;                      select text from user_source where name = 'PROCSELECTBOOKKINDLIST' order by line;    SELECT * FROM DBA_source;     SELECT * FROM ALL_source;                                                      select * from all_objects;           --OWNER='GEOVIN' and    select * from (select dense_rank() over (order by object_id) as dr,b.* from all_objects b) x where  dr<=15;     --存储过程 select * from user_objects where   object_type   = 'PROCEDURE';     --http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm --Oracle / PLSQL: Retrieve primary key information --GetPrimaryKeys SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cols.owner='GEOVIN' ORDER BY cols.table_name, cols.position;   --- SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = 'BOOKKINDLIST' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cons.owner='GEOVIN' ORDER BY cols.table_name, cols.position;