sql:Oracle11g 表,視圖,存儲過程結構查詢，oracle11g存儲過程
--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;
--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;
--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 = '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
