SQL: Oracle11g table, view, stored procedure structure query, oracle11g Stored Procedure

SQL: Oracle11g table, view, stored procedure structure query, oracle11g Stored Procedure

-- 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 = 'ployeelist' -- 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 = 'ployeelist' 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 cols where cons. OWNER = 'geovin' and cons. table_name = 'ployeelist' 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