数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]
作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象1 U( x5 ^2 a0 S  ]) M: p
原作者姓名 Fang</FONT> </P>( r, i$ g0 R) e; m0 S
<>SQL SERVER
9 t+ a. \3 l3 G# {1 A) D+ Q取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法7 D4 H3 u. |0 }* @9 @
系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>1 T# Q" }  B  y" ]0 w4 w: e2 {# N
<>或者</P>
- K) F$ R0 g; M0 e% H/ B% P/ E<>USE master</P>2 |6 ]6 j0 Y  [
<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>
2 o/ D4 z! W0 X9 G  M% m( u0 A  N<>8 Y, _. S9 w  b
sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>/ {9 t5 t+ S2 W+ e. _  a' a
<>取得表的方法
* _# s& c( y8 g, R" M系统存储过程</P>5 s3 u" U. }4 a: Y7 [0 p
<>USE xxx</P>
* z5 j) S8 K8 |' m: c<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P># u) ]8 K7 u3 h7 o, Z2 p, t
<>或者</P>
7 ~* m3 `8 T7 o1 D, `# V<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
  Y  V0 i: o5 e<>USE DBAudit; d/ Q' {; o6 Y" Q' ?
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>
. a$ a2 g( u* U4 V2 _9 l<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>
" {1 a" K( ]4 t$ z<>USE DBAudit
& Q4 D& u7 K" I9 }* q0 q9 XSELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
* ~  v% y6 r$ J7 u9 g" c& \<>或者统一使用：</P>
7 ?- @. u+ \2 y/ m% g* @<>USE DBAudit</P>
6 H7 I, y. z+ x<>SELECT sysobjects.name AS name, sysusers.name AS owner, type, sysobjects.id AS id FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U' OR type = 'S'</P>
/ x" s( i% Y" m8 C& e) H6 w<>注：</P>. H) Y# J. ^$ U0 k  c* Z, K
<>sysobjects中type字段类型解释：</P>
) \; M' K' \. m$ J' Y: L<>C = CHECK 约束</P>% _. }4 M) e5 j( }. a7 \
<>D = 默认值或 DEFAULT 约束</P>
4 B( J* B& l! s% [' l<>F = FOREIGN KEY 约束</P>0 r* Z! Y/ a' a
<>L = 日志</P>
' k8 f- \7 `: {  u5 Q- F3 {<>FN = 标量函数</P>
0 n: s: F0 V/ h7 n<>IF = 内嵌表函数</P>: I7 o/ k3 X0 [# p# [7 e
<> = 存储过程</P>' p$ m1 w2 ^+ h! a& h" D! n
<>K = PRIMARY KEY 约束（类型是 K）</P>
7 S- j) {6 }! J<>RF = 复制筛选存储过程</P>
6 q4 p1 G  h  o<P>S = 系统表</P>$ y) |0 A6 U8 M3 s: K( W
<P>TF = 表函数</P>4 b1 j, {( Z" o, k! a& d; U
<P>TR = 触发器</P>
" M8 I7 O" C! h3 ^/ J# P<P>U = 用户表</P>& Q3 i4 ]$ R5 ?8 C. x
<P>UQ = UNIQUE 约束（类型是 K）</P>6 s0 d/ X( q0 ?/ [% {/ m' }
<P>V = 视图</P>
9 E% X; a- ~9 s1 W5 @6 D/ h<P>X = 扩展存储过程</P>) u+ n/ S. p9 |4 B
<P>取得列的方法) N$ P9 {' `; j, d6 j
系统存储过程</P>
/ K3 R8 X# n) _2 L5 L  h<P>USE xxxDatabase</P>
# W! x5 G( H! M& B/ Q1 N* \<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>- p  T) F( \4 d0 }% r
<P>或者</P>
9 X0 N# ?& |# S5 C<P>USE xxxDatabase</P>
4 Y1 s3 [+ Q" n& w6 M0 a: z0 I<P>SELECT syscolumns.name AS name, systypes.name AS type FROM syscolumns LEFT JOIN systypes ON syscolumns.xtype = systypes.xtype WHERE syscolumns.id = xxxTableId</P>
4 f& T" n  t4 W& I# g4 r  k<P>这两种方法都可以取得包括视图的列。</P>; |( O3 j( f; v* |% O
<P>取得视图的方法
3 b8 [  `1 v/ {) \# b7 t系统存储过程</P>
9 J  P' j, e8 W7 v<P>USE xxx</P>5 h5 Y$ P  G( i* U* `2 g8 G
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>* B- O3 j+ ?$ W( K: [2 p  B# X% G3 z
<P>或者</P>
! r1 M, b3 Y2 d( Y6 z, @6 A& x" i<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P># M; ?% C2 t9 C0 c9 [* F6 Y, m  d! F
<P>USE DBAudit</P>
: A0 b6 r3 J% q0 e& F3 Q<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>
- j* l) h" V0 O6 N8 e2 X+ |6 b<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>8 ~" Z! C8 E; o8 Y  i# P9 w
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P># p. e7 @4 [4 |+ v! W. C; A
<P>取得存储过程的方法+ }+ J, q$ m  b" ]: z9 W
系统存储过程</P>0 |, M3 K# l: M  d- z
<P>USE xxx</P>
8 y  q3 f- X. t. o<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>+ _5 A6 T, P' [" ^% o: a4 d, ^( n
<P>或者</P>" z3 [' o1 L9 l" X' r* ?) G
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>" W' K# U( \. P0 w( ~8 `
<P>USE DBAudit</P>: Z, H( q  w* w- o7 M% l9 R
<P>SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'P' OR type = 'X'</P>
1 r' g9 d9 J4 B9 M<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>. W- i" E3 x; W' ?9 ^
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>  {3 c0 t( k! Y
<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>
3 Z, t/ O) B6 b* _! l* @/ H<P>取得函数的方法
2 X6 W% C+ w  ~" ^6 _" C2 b- d! n3 l系统存储过程</P>
/ u; R! }5 E5 M; @<P>USE xxx</P>
8 m; K  ]) E/ j* y: h<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
' n3 V4 w  T) W' \% m1 s1 O: v* h9 b<P>或者</P>
$ X% T9 H7 p3 r( G  b<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>" ?- L3 H/ M* N
<P>USE DBAudit</P>( l7 k1 k" E& N. C. B
<P>SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'FN' OR type = 'IF' OR type = ‘TF'</P>
- _$ ?1 e1 B  U# d& k! H6 k. K<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P># \( h  {7 F* s$ v/ A" q
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>! u. D7 ?% {5 O, ?) o
<P>只能获取用户定义的函数内容。</P>; Z, K' p9 z. q: q+ y5 J6 W
<P>取得触发器的方法6 ~5 y6 M) ^4 P
系统存储过程</P>
" C' }& E$ \" t* B% s9 s! \! h<P>USE xxx</P>
+ C: T, x# Q1 Z3 p% q<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>4 }+ K. j% R4 V) s! u
<P>或者</P>
; I3 d5 S& m9 }5 n. L8 c: }<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
8 [( W2 F) j( A8 N( ^5 r& o; l% N<P>SELECT sysobj1.name AS name, sysusers.name AS owner FROM sysobjects AS sysobj1 LEFT JOIN sysobjects AS sysobj2 ON sysobj1.parent<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>obj = sysobj2.id LEFT JOIN sysusers ON sysobj1.uid = sysusers.uid WHERE sysobj1.type = 'TR'AND sysobj2.name = 'xxxTable'</P>7 P: v- C$ E. E" O( `6 K( _
<P>取得索引的方法
7 g0 K  `+ m9 }. ~7 s1 K系统存储过程</P>
2 ~- s  Y# G8 K) f<P>USE xxx</P>
' a& p( _/ s. A% O! H<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>
8 I. {% l- P! L<P>用户
, P3 B' d- ?# Z. s+ x0 @- f系统存储过程</P>
- ]+ }, I( k4 ^. e5 O  ?( h% y' Y<P>USE xxx</P>5 a5 S5 X, \; q& x, A
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>; `1 z8 }, J% D9 @% M5 a
<P>角色6 ~0 m. `" \9 q* B
系统存储过程</P>
, `: f$ T; ~* H+ }5 Y<P>USE xxx
' l( s) Q, s! A, k; Z; E! OEXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>& E0 f  r9 n( l. A/ a
7 m; t- n' k5 |  n
<P>ORACLE5 {" I) u! v- F3 h' g- z
特殊+ g% f- u" g6 f) b. }5 m/ p5 m
&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
" F/ k! f" e: a' h- _# H( D3 V  @$ j<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>
' s) g' x1 R, F" w, ?<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
1 g1 W* K- d  _3 }<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法  f9 U2 i- O. q. R9 E: j; a
只能由用户指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描到特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A><a href="http://vip.hackbase.com/" target="_blank" >服务</A>器上的所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>。</P>* x6 d3 V, `$ `& L( \$ K! Q) r
<P>取得表的方法
4 N5 |( i0 J1 V存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>
9 j! V& H. @8 ?4 Y) G<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES</P>) {; W9 `8 n* b1 c1 H! B
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P># b5 p8 x9 N4 W( S' P* r% b
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES</P>
/ ^, `2 {5 y9 w. r& L<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>
2 k5 W6 C' |4 v- l# Z  l<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES</P>
7 N* Y" w  D& v5 W8 \$ v<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>
& o3 d% ?9 ?3 L. i  M: y; y" W<P>, g5 [. |6 F8 ~# [5 M2 m4 ?5 Y( N
DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
7 G. S' T2 W# U. @  M<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>/ T$ P; P6 R) ^7 F
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
8 h; C1 D) M8 E9 I# `<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>
* R' N; r+ A0 D4 T- Z5 F+ {<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
' F. `% d7 [# q! @$ n; L/ d<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>& s* Q" n# N4 {; y$ P8 x
<P>
( D3 e* _9 ?7 g# F3 W9 W. G因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>. Q2 f2 m% q" v# n" l! ~
<P>SELECT TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES WHERE OWNER = 'DBAUDIT';</P>
6 W7 X! w* m* v" C* a# \; a5 O<P>取得隶属于指定表空间的表：</P>; k" c. q# D% U/ ?; \
<P>SELECT TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES WHERE TABLESPACE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'DBAUDIT';</P>' k" n+ I  q# T2 ?. Z, w
<P>
& H& p9 W3 [+ J) A! a也可以不指定用户名，从而取得所有的表。</P>
5 r5 g( L: f, F3 m/ ]: [0 P& v- B<P>SELECT TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES；</P>; ~& f( C3 e$ O1 \0 R# [- V! t
<P>取得列的方法8 P! |  Q' Y* w; r
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>6 A1 w' {; d# ^# @2 S; u3 f
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS( _9 P3 P2 Y/ H" N
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。2 G" Z# V# {0 e3 C- \7 O
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS
( o; H0 [- m8 y" |( n# j1 a描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。* {3 b" x* j+ f+ A* e
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS
2 o" b+ g% [. Q5 y描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。+ }1 D8 X% ]# [1 z  k
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLS</P>
& e' }( R" J) o# J# ?. ~<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
. e) k- p) G) p# V# N<P>
/ U0 l& E# T- H) I- E因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS中取得隶属于指定用户的表：
& t6 p, E- m! `SELECT COLUMN<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS WHERE TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'TASK' AND OWNER = 'DBAUDIT';</P>
9 [6 O2 L+ o6 E( w9 `3 k" T<P>取得视图的方法3 A# t* l; p$ }9 y' Z$ Z
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
, M  F( X, d2 Y<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS! f' Z$ r) |9 ^, {
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。
0 H) O) `% u+ vALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
. i/ d2 {+ {3 h: U描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。$ u: n' y5 z0 u" S5 y
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
& d1 S7 J6 s/ I+ P7 A<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>
5 [+ I, ^% u% a* }7 N) S( m- f<P>5 B* m# j" f! c
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：7 D% K0 d5 m6 z
SELECT VIEW<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS WHERE OWNER = 'DBAUDIT';</P>& S' `4 z7 b1 c! F2 Q+ t
<P>取得存储过程的方法/ c7 D- s8 E# d+ l8 a2 m/ x4 H
存储系统对象的系统表/视图有：</P>9 f: {. `1 j8 A  C6 r/ l
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
$ b: ^4 f3 v2 V5 y3 t<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>' M) {* {- g0 S* _' l% }0 h
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS4 i0 j7 g3 I9 k- w% h& T3 J
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。# e, w# X6 k* P0 d
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>0 ~7 v* f, d# Z; [- \
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>
. i: \6 }! c; ?$ b' {<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>  @9 m4 X. l3 K$ V' o& u# }
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>
3 |  _# V  c! X. W<P>+ Y# H5 Z$ g0 u
对象类型有：</P>+ d: M+ y9 s% n& M8 o1 w( i& [
<P>CONSUMER GROUP</P>
3 X. z0 E* L$ J<P>CONTEXT</P>
6 |$ Z5 Y2 J. l% t<P>DIRECTORY</P>& \0 l- Q* c1 u! t) g4 k  c$ S
<P>FUNCTION</P>) A; m! ^3 J0 N1 I1 k- i) f
<P>INDEX</P>3 d; o  J/ B* L, P5 d# Z" \
<P>INDEX PARTITION</P>
6 B. e7 Q6 k6 t7 S, g- N5 S9 M6 K/ Z0 F7 ]' Q<P>INDEXTYPE</P>
1 _+ y* M0 Y. S3 e! u<P>JAVA CLASS</P>
9 _# U5 L; g# x) C# L<P>JAVA DATA</P>$ L. K% c/ i# u  @+ d
<P>JAVA RESOURCE</P>
2 @0 G% g* X! J& q( b<P>JAVA SOURCE</P>
! v, [. q( z3 v/ W  y<P>LIBRARY</P>
7 ]' T- e9 S/ M/ C<P>LOB</P>
$ F  ^/ J5 q/ Z4 g$ x$ a<P>MATERIALIZED VIEW</P>7 G$ P, e9 x1 y2 G5 O9 S
<P>OPERATOR</P>" |8 B2 m: s& @  D, g
<P>PACKAGE</P>
9 c. j+ l, m  r8 J  Q5 }<P>PACKAGE BODY</P>
: s* J1 g2 z1 f0 w9 p<P>PROCEDURE</P>! x7 p3 z; V( c; b5 Q
<P>QUEUE</P>
) _5 w* ]/ Z6 q3 K2 s<P>SEQUENCE</P>
  Q, F+ n6 g$ M/ C# Z7 J% Z& ]<P>SYNONYM</P>1 q! R  z# V& h* Q
<P>TABLE</P>
8 G! k' W; d& Z: T! T<P>TABLE PARTITION</P>: t/ G3 J$ K( U8 Z# v$ W* n& ^
<P>TRIGGER</P>3 [- S7 l' z0 O
<P>TYPE</P>
$ |( S0 K# ?& g7 d" f- \- p3 Z<P>TYPE BODY</P>
7 M# H5 @3 ?. I<P>VIEW</P>3 Q4 k- J' d# `1 U
<P>
/ I/ ^  T1 m* ~( Y5 \: ~6 m因此，取得存储过程可以用：</P>
( t( d& e8 m% z% r<P>SELECT OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS WHERE OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TYPE = 'PROCEDURE';</P>! X  [9 S- s5 ?
<P>取得隶属于某个用户的存储过程可以用：</P>7 [. A  p2 E; S
<P>SELECT OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS WHERE OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TYPE = 'PROCEDURE' AND OWNER = ‘DBAUDIT';</P>
9 k  u- G; [& p/ [3 P/ ]! s<P>
. T0 M9 F$ t' r同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>
+ P  C/ E/ n6 V' @4 d2 P<P>取得存储过程内容的方法
' s6 e% y$ m, g& P9 t对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>) y! J9 y7 c! g# x  @9 q8 o$ P
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
7 h  f+ \( U; `9 O<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
4 `; l- R1 k& o7 g/ v4 v2 ^$ j<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE6 o/ i9 L; _1 n6 c, n- D* t5 n
存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。% f4 V# p) k' Z+ C' r
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>* l1 h7 Q& n5 ~/ M* C
<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
# P. n4 J4 e. |9 l  x1 o# q<P>6 y: a1 Z9 }- A5 V) o1 Q
因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>2 d/ W& @+ o; v# k( D
<P>SELECT TEXT FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE WHERE NAME = ‘XXX' AND TYPE = ‘PROCEDURE';</P>( c" K# l- D1 |+ c' e- ]  f
<P>取得函数的方法
# S; x1 `- ~+ o2 d4 U同上，取得函数可以用：</P>- l+ X5 C- A4 B1 l8 n5 v3 P3 |# G
<P>SELECT OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS WHERE OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TYPE = 'FUNCTION';</P># R( J" U" S+ o4 F$ r& |
<P>取得隶属于某个用户的函数可以用：</P>
. m# a2 F8 `7 E. z- B<P>SELECT OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS WHERE OBJECT<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TYPE = 'FUNCTION' AND OWNER = 'DBAUDIT';</P>$ _, h6 c) V0 C" J) n8 o
<P>6 K% s( ?. t3 t7 ]* @& D
取得函数内容可以用：</P>
& w6 D1 j; c( B9 q<P>SELECT TEXT FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE WHERE NAME = 'XXX' AND TYPE = 'FUNCTION';</P>4 O" k; ^$ J+ c
<P>取得触发器的方法8 W4 c6 f" j) @) k
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
" a; e( Y$ q% n( j. |" t# m<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS! g) z6 q2 U" C2 ^* a
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
, _! `1 C8 X6 [9 s: c# rALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
( X) C+ v! c3 Q9 \描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。
8 ^- B+ ?# _+ e: yUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>: b. k$ j6 ?+ y  }
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>) `9 z# K2 d0 Z! N  M+ P
<P>
% _  H% o: N0 u5 i因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
+ d9 n! {% X9 W0 n: E* K% B: ASELECT TRIGGER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS WHERE OWNER = 'DBAUDIT';</P>
8 W, h. o+ J5 C- L8 h<P>
# A6 @' v# n. I& o! e# H取得触发器内容的方法：</P>
! {$ D8 T# `' |+ a5 `% N# |- q<P>SELECT TRIGGER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>BODY FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS WHERE TRIGGER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'XXX';</P>
! C% H: I' i! o1 B! z: F) V( @<P>取得索引的方法
# A) O. P5 v0 p存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>
% W. K* W6 P2 N5 X3 {<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES7 {" D% q6 `7 L& u" |5 v' a5 _
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。! l! e8 Q) g- t
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES& F  ^+ W8 O/ s& v5 L& d
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。# G- A4 |  W2 U' Y- j/ z6 j
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>
9 X8 j* e& u! N/ ^  ~4 X) a2 b! c<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>
0 @( f2 G' }1 h6 \: e<P>
/ t8 b! O4 k4 C! i' @$ C. I因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：5 b% w8 s* H% n) P" ^+ r) B
SELECT INDEX<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME,TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES WHERE OWNER = 'DBAUDIT';</P>
+ x, s$ k% W/ U! r3 k. G<P>. q- c: t$ Z* ^. G- S
取得索引相关的列的方法：</P>
. ^, u0 W" U; Z; n# w- D4 X. B# |<P>SELECT COLUMN<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>IND<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS WHERE INDEX<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'XXX' AND TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OWNER = 'DBAUDIT';</P>; Y) B4 M* g' V- l
<P>用户
; e5 s" V* p! \( k存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>
4 g" P0 Q# G$ m5 y& V9 L4 r' Y. ^<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
# p; I3 p, c& k, z) k描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。2 g! ^; B) K3 ]
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS6 w0 e7 ]! n7 c& k3 I  x/ }; n7 }' ?) {
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。+ @, D" {/ o/ Z2 Z. ~
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>
, J7 ?- c2 s/ y1 U<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
  O7 Z6 `) I+ _) O<P>
& M  g# x8 e8 p因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
% y1 w+ X6 Q3 |; y% E) A1 SSELECT USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>ID, USERNAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS;</P>



欢迎光临 数学建模社区-数学中国 (http://www.madio.net/) Powered by Discuz! X2.5