数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]
作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象& U! r( s8 m4 f2 D$ T4 }
原作者姓名 Fang</FONT> </P>) E. T" `% @7 G+ }7 L, H. S
<>SQL SERVER5 d- n+ Q5 Q  U
取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
1 Q2 {, ~+ J5 u* p系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>" m( Q+ n* H. k# U/ H$ ~6 M6 ]
<>或者</P>
$ Y# ?- {( l- _& c<>USE master</P>, C' [/ s- B/ l% S
<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>4 f7 R- X1 b/ w
<>1 @* d- `$ h' a+ @7 s. \
sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>
8 {' V# m6 Q5 j9 Q' _+ q<>取得表的方法
# _  J; G7 }7 e. q, N# s系统存储过程</P>' w4 y) ]  E! k; P! k) j. n
<>USE xxx</P>
1 f/ e$ M% R* [<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
7 s! N  c2 U. ~4 y6 S& E<>或者</P>! ?7 N3 }/ Y) ^8 }1 {7 g
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
( H$ R& E2 U3 L9 B<>USE DBAudit
  n6 H& L9 H7 \2 k; K1 N5 ySELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>
* t- j! G# r- u<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>
/ S8 m: }, h9 `6 H( |7 z<>USE DBAudit% l" V* p* t. w; [( E8 D6 B
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
0 O7 f/ [9 R/ W' z6 m* b<>或者统一使用：</P>
6 l( @9 `* u+ Q: K% ?5 X& M1 a% O( k<>USE DBAudit</P>
5 i/ ]. h2 `6 y' L<>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>
' B/ D' @* _+ S<>注：</P>, N1 B+ X  d. z4 e. Y
<>sysobjects中type字段类型解释：</P>* o6 d7 ~3 a+ e6 c
<>C = CHECK 约束</P>6 w0 V4 e5 t' m) h# ]
<>D = 默认值或 DEFAULT 约束</P>
+ E$ h% \8 }; o<>F = FOREIGN KEY 约束</P>  a5 V5 ?5 c# c' @  m( k
<>L = 日志</P>( m, n! ~) u, f5 n, C/ i  h) \
<>FN = 标量函数</P>( Z- i& G6 {, x
<>IF = 内嵌表函数</P>
) s* n5 a8 M& d+ b$ d1 B) ]<> = 存储过程</P>! w; X& P7 k* Z  @- \* z' e6 R
<>K = PRIMARY KEY 约束（类型是 K）</P>
- P7 G* z4 c3 }; Y/ B& }7 m<>RF = 复制筛选存储过程</P>
( Z- c. k% }6 s, {  f; N0 E<P>S = 系统表</P>2 Z. A( B5 {+ `0 `
<P>TF = 表函数</P>: ~0 Y- k: X% b" y. s; h
<P>TR = 触发器</P>
2 e& o2 h( F/ }: U! [  c1 H6 N! U<P>U = 用户表</P>
/ v! S* s5 _- f3 j8 ^4 P: u<P>UQ = UNIQUE 约束（类型是 K）</P>
9 M& S; \1 y8 J' I5 z<P>V = 视图</P>
0 B4 O; j6 {$ h<P>X = 扩展存储过程</P>2 h0 z! j2 D0 k9 Q; C
<P>取得列的方法6 C( U9 |& v+ a; ~* E3 |1 z) ^: U
系统存储过程</P>
" ^" D% D: m) ]( ^! Y<P>USE xxxDatabase</P>
4 G! {- Q5 q% C; K<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>2 C  u( Z8 W( w4 L2 C/ r
<P>或者</P>9 |1 E# W$ M* d* E# S& \8 G3 o
<P>USE xxxDatabase</P>3 I, ^8 l  Q( Y$ J' d
<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>
2 _" f5 a8 G4 q" C2 j! [<P>这两种方法都可以取得包括视图的列。</P>. h' y: V; z- X' v7 p; g
<P>取得视图的方法
( V* r' ]" w& X' o6 H系统存储过程</P>
2 w8 n4 X( n' e/ t3 d8 J) q<P>USE xxx</P>
7 m4 i. g: v' R<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
; A4 i9 X& t0 b9 _<P>或者</P>
; j9 q  x. U7 ?% v+ p, T1 D2 G% N<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P>
/ n) ?5 c% `( y6 o  X% t' Z5 }) D7 X% @' x<P>USE DBAudit</P>( a  p9 B7 @9 Z  ^/ h. M
<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- [+ ~" @) C<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>0 q% e8 W* r! c* o- O! o1 Y
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
6 D7 K# T9 H9 Y; k1 p<P>取得存储过程的方法
2 ?/ A7 a* q. O& s1 p- ]系统存储过程</P>$ m0 r  H; n0 u1 L1 }; }
<P>USE xxx</P>5 W5 `+ {/ p8 K/ u# _% d
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
9 {& A: `$ g7 h. ^$ m: L8 d<P>或者</P>" Z+ G  ^  c) g+ q" F
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>
6 d5 r6 l& Z: d$ U<P>USE DBAudit</P>
* w. d, S' w6 \( Q<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>
' Z0 U- k$ a: C2 l: V<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>. k8 \, _# G! W, E  g  s" P
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
* p7 U8 V8 c- w, z<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>6 ~$ `' g0 t5 ^* S. V5 \
<P>取得函数的方法
" _) I0 `' t5 D1 w  j系统存储过程</P>7 M- ~, m  l  k+ o: p
<P>USE xxx</P>( H- B* l8 G+ J
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
5 L; i# }8 {& P) e) \7 v<P>或者</P>* \: w6 @) Y* u3 s& O1 f% p( s
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
" j+ g: s+ g$ h: B1 x/ {<P>USE DBAudit</P>; J% n/ x: z: z! n9 _3 S
<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>2 G% x* u4 ?* u+ B/ ^0 y" e1 j" `
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P>
7 _5 F) _* L  ~5 l<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
" ?; y1 \. T; L6 A<P>只能获取用户定义的函数内容。</P>
9 p# U* D- b: F" _8 d" {<P>取得触发器的方法
" |6 I6 q' W4 h系统存储过程</P>
9 G$ ]" X$ U+ D( e<P>USE xxx</P>& d) ^; ]$ [! g9 }0 I1 ?2 |  p
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>7 T. V2 ^( q9 l1 J& W$ d
<P>或者</P>+ y( Q6 c, ?% Y8 Z3 T2 h
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
9 [" ^. P5 n# X5 u0 B<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>
! ~- ^+ B3 L) G1 {$ w<P>取得索引的方法7 _& t$ v# y$ u* x. `7 @; l
系统存储过程</P>5 w! ]  R$ \! Q4 E- G" S
<P>USE xxx</P>
& R$ h1 i2 d  p: @<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>$ A. ~7 y, H! Y+ o/ M- E* v
<P>用户; o( ^! v# {/ \) Q, ~8 _- Q) [0 O
系统存储过程</P>% `4 k$ G7 J1 i1 }' t
<P>USE xxx</P>
/ w0 ]1 F2 O% D0 n, P' L<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>0 i* g4 Q% C& A& w: [
<P>角色
+ \- {" @- Y9 ?系统存储过程</P>% T( C* V( }) h  E) Z3 z8 k0 r
<P>USE xxx
' V) M7 J% K$ |EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>6 Y4 \) H6 Y& T/ g

+ Z6 O3 L. t& T; J5 S<P>ORACLE( a7 ^" v& `2 r& ]' W
特殊8 M- X0 {. \1 l9 |
&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
* Y) ^9 N0 c0 Q- v" x2 V8 v<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>" g6 c# W2 I) @7 Z; ~
<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
- Q: U  }" `6 Y) k<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
# }( l7 x2 ~. T7 d只能由用户指定<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>
+ s8 Q* z$ c. R: K( r& r( [<P>取得表的方法. I4 I8 a6 d: ?: l' y, v* g
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>1 c$ t9 [% t: z+ {6 @3 E
<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>' }& N; q" U, G7 w' C# X
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P>
+ ~* K2 n% \; ?; N, P<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>; p2 a$ V; I1 z- }( c) D5 P5 `
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>
$ x' B; m3 [& ?4 I- m<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>; Q! m( y1 M9 H; ?7 A; f7 v: V
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>9 T. i+ O, v  K( l
<P>$ j4 c: D, n1 X6 g& L+ H
DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>/ I  i$ q) e8 M2 u% w: H" C, b
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>6 `2 o7 F9 Y( e7 }3 `  Y# v$ n
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>' E. G1 G; w' ?' \8 I
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>/ ^2 [' x) P7 Q% j
<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>1 M% [8 L" ~9 `( N1 o( S
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>
) h! @* \+ h& @! d4 z* S. v; V& N<P>7 }* g/ Z# m, w
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>
! V; V0 _. ~( }& v2 q2 A  d- y3 r<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>
+ z+ |  v& v9 a& j2 `$ C! Z<P>取得隶属于指定表空间的表：</P>
  c, d7 n% [1 [1 S: g+ _6 j<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>" a7 y0 t5 I; ^' N
<P>+ l9 u" U8 P/ U: G; ^3 S" y- x
也可以不指定用户名，从而取得所有的表。</P>" \' u9 Q. A1 q4 G5 M
<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>2 V' i* C' N: D. s: q$ |
<P>取得列的方法: O; E& t, c2 x# T
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>+ X' ^' L8 T: C9 I3 h
<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$ c, w5 ]) j  u. b, H
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。
4 O8 x* @+ P! A3 vALL<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
5 R- J" h) m4 u( l6 d& W) S. e描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
& D8 v- o( @1 z: D+ TUSER<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* r! `9 K1 L' I2 `
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。
3 s2 E# b6 i: y6 [# sALL<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>3 w- J' o- \( W5 e* [2 I
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
# B) i/ R$ ?" z4 X& }# ~: Q<P>
8 J8 {! M9 `( `# G$ m& s% L: g4 R  c! w因此，可以从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中取得隶属于指定用户的表：3 b, Q3 V6 N4 E5 T/ \( O
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>/ G6 H7 d, U  ]5 M8 N/ F! R# o  j& Y' ]
<P>取得视图的方法
* D( i+ I8 s  ~& b3 ]存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
9 t( U9 R9 Q8 d0 `, _4 q5 ?8 P<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
6 m# E5 I& y% E& Q描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。
4 r! M! u- q  kALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS( {; s, ]( ^& B& h4 j- M
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。: M. D+ U- A! K
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>6 v3 o5 j3 J0 Q! f
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>! z5 Q. F: ]8 x6 t% d) M
<P>
) w5 \/ O' [; r0 `0 g' a' C因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：& g# C) U7 K0 _* [/ H& F
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>
9 i- w2 [: l& F; _" S8 v<P>取得存储过程的方法
+ ?  r: y; O0 p7 ~: m8 p存储系统对象的系统表/视图有：</P>
. K, i. z+ E% u) l+ w<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
! j% p% d' O, y) y<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>
* i2 g: {8 q/ j0 f9 q<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS3 ?( b1 Q) l7 o7 E' j% D
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。
! R6 I4 H* p. l9 XUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
, t7 @+ R. y( D5 `<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>
. x1 l( d6 g/ B6 s: q<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>' v. J. [* ]( o1 v1 ~/ U, l
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>7 ?& r1 i, m. e7 J) z
<P>
" e: X) A7 i; D+ i( M- J对象类型有：</P>1 e* v! _1 N0 Y9 B- u
<P>CONSUMER GROUP</P>
" k: i# O2 x/ f) x) V2 l" e5 p( x<P>CONTEXT</P>
/ N- R6 q. {+ }% E4 g<P>DIRECTORY</P>
* g" m/ Y( y( q$ X4 [/ ], [4 C& ]0 l% Z<P>FUNCTION</P>
* e$ [' F- Y- I( j<P>INDEX</P>0 h1 ~6 B3 h) M: L: K  Y
<P>INDEX PARTITION</P>
- N- S- X6 |+ ?& u$ _<P>INDEXTYPE</P>
9 D' M; u$ P) D- [+ E( u& |; W<P>JAVA CLASS</P>, Z& ]/ Y$ R5 q. x- V" L2 {
<P>JAVA DATA</P>2 h, y3 ^: y) f9 M4 O/ K$ s& |/ Q
<P>JAVA RESOURCE</P>, p; ]5 |; ^6 A3 z/ _* ]; Q" o
<P>JAVA SOURCE</P>" f/ f% f! s' a, X* \
<P>LIBRARY</P>
! V% K& b5 X- P; B0 ]1 U<P>LOB</P>
# Q6 ]5 T; i' `' D% q<P>MATERIALIZED VIEW</P>
0 z% W+ s. d; h7 |<P>OPERATOR</P>5 r! I; y4 J5 H- \( a2 f6 m
<P>PACKAGE</P>
5 ^# J' @$ S) u+ v<P>PACKAGE BODY</P>; k. S9 D- h# D  k0 c- P
<P>PROCEDURE</P>
  w7 b  R3 N! P  I<P>QUEUE</P>. {1 U8 L1 w; g! s; A
<P>SEQUENCE</P>$ E8 b. ?# g' A2 O  U" d) r
<P>SYNONYM</P>6 Z* b3 s8 s/ U- D, @# r
<P>TABLE</P>/ j! `$ e! Y0 L# `% M; u9 a
<P>TABLE PARTITION</P>
0 d( h  w. H- n9 t. W<P>TRIGGER</P>
) s" u- t2 u& H<P>TYPE</P>
- E. x1 P4 c- X7 H8 k* H7 E<P>TYPE BODY</P>! Q& `4 {* x  g7 k" A
<P>VIEW</P>
" P* H* G* I' e; V; A<P>
' F4 [* j# |5 {6 x4 q: d* g" e因此，取得存储过程可以用：</P>' I( E6 ^: k. F7 ^! I$ i" H
<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>" }- Z; [2 W2 x2 _2 e. K& q
<P>取得隶属于某个用户的存储过程可以用：</P>2 j: H; l- P$ A8 h8 `
<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>! x: S1 J/ l" @+ T, |  R6 e
<P>3 |, F" P; r  n8 i! f
同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>
% P( Q4 g( G! C' i* v2 j<P>取得存储过程内容的方法
5 X. S% o# \* \对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>
0 q0 T$ \3 M% d6 f<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
, @$ u3 w/ P( \4 i. O<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
$ L; O0 l$ t$ N: U  O5 R( \<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE$ H! S) w0 q5 n$ ]3 y
存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。
7 T( Y3 w: ~7 z# h7 U! i0 OUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>$ ^$ |& v) P4 n9 q
<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>* H1 c4 V! P% o
<P>
4 F) O: u: m6 k" [; m7 N因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>
7 q4 ^( h4 U+ D+ k' G$ B<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>
, o8 T- X- j* D$ ^% D3 a. i<P>取得函数的方法
1 F7 H0 A6 ~" H, c同上，取得函数可以用：</P>
4 m0 w" {- G1 Q" Q& j, |+ x  K<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>4 `( B! E, z% ~, f5 F6 }& f. Q; A$ [
<P>取得隶属于某个用户的函数可以用：</P>
) V& O8 P0 e. s# t/ s+ u+ g  p<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>
; d' _8 T& F. S<P>2 v  o3 f9 {- O% u, l$ x9 j1 G9 z
取得函数内容可以用：</P>) V' s! |/ w& b* w
<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>
' J3 M* a+ ~9 [2 c; B- [0 x<P>取得触发器的方法
# e% H1 k, E( v; ~' d. a存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P># x; Y' z& H3 }+ S( j
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
0 P9 @1 H$ {: |0 Z  s0 H描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
* F) q1 H7 S# w2 h! `ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS; v& t9 Q, u3 ~
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。
3 ?3 M5 ]9 b& IUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>
: `6 E- _- R4 P- C9 R4 R1 K<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>
8 T7 \  E4 o( P+ f/ v# y  R<P>- h: A' v9 x4 H; Q* e
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
( i* b* z$ G& N  {# n# {SELECT 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>5 p% R1 p2 @* g. \; c  F
<P>9 z3 }7 p5 E( |% j3 T) N3 A" N
取得触发器内容的方法：</P>
# W4 ~& q1 b( ~! t7 z$ V<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>9 ^$ W0 `" E& L7 z- p6 O
<P>取得索引的方法9 z4 i9 x9 E9 G' z$ w" Q
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>
% J" m  o+ p8 m2 N7 L! o- w6 X1 U<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES
( O7 O# R; s# y; a0 Z描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。9 l( Z( G. Y( s: q# b/ N8 E" i
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES8 v. T* s( [9 W
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。" h- e& d1 D  M/ H+ u( ^4 b
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>7 q$ N- m9 A# i# @$ I
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>
5 f  z- r3 @, H9 e& F6 c<P>
1 C' C9 R) y$ q$ d+ C/ _# q0 F因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：3 i- k+ C" i' J- I9 E! k
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>
+ ^. e0 r2 J  Y# U. y<P>) m& N; K2 W  {( T* A& ^
取得索引相关的列的方法：</P>6 B1 ^$ S8 Y0 m. B  t+ c' R( |
<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>
$ `! ?; [8 Z% `5 M, ?7 i0 ~<P>用户
6 r% T( L+ y* R% G5 c& J# c2 ~! p存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>4 c) x8 k# M# b$ l
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS$ M4 W$ \* O7 l
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
) f2 s$ C+ Z  AALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
7 Q1 a! l* f7 Z% ~* _7 j! ?描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。7 e6 ?9 d7 P: d4 R+ b
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>
) g5 U- v3 I* l- Z/ ^# w$ |<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P># I4 v% h; x4 }$ y% o3 s% v
<P>2 e1 d1 x# i: M9 U  E! N. @1 _( i
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
) y  F1 d7 m. KSELECT 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