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

Source: Internet
Author: User

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

  

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.