-- 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;