数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]
作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象, r$ f/ z) o7 x3 |
原作者姓名 Fang</FONT> </P>  J. M/ M: y3 e# H2 h( B
<>SQL SERVER
+ I+ [: ]- p6 k取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
! `+ k2 X  e+ b系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>! p: a" K* b) W* V4 ?) }* \# ^
<>或者</P>; ?# E% m. p& A) v- c) _
<>USE master</P>
. z$ a' e0 c" G% x* i: b3 L<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>6 P. [# Z) c/ g3 M2 V& e5 N* b8 R# T
<>6 q) m% V) b$ U! }+ ^3 y
sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>5 w9 a2 ]8 r8 h. _3 r
<>取得表的方法
& \7 D/ z6 d6 n7 a  o" p系统存储过程</P>
9 h! T0 `( j" `' L$ U$ C* M, U" Z<>USE xxx</P>1 |$ P4 T) y+ ~- T2 k
<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
" r, k3 E! J( m9 `) h0 ~; k* _<>或者</P>9 X3 c- V+ {$ e" f; @  ~
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
8 g' _) w$ n0 n- B1 M( T<>USE DBAudit
8 C, x/ l; g8 {+ ]" l9 R0 a, TSELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>9 L0 W8 w8 V- ]$ K! S1 m. c  [6 Q
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>
: B9 A4 u6 J- ]# P1 S% {<>USE DBAudit
; k4 I+ Y0 c! A8 E  h" USELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
0 s. W. D+ l* ~) j* ^' x) R<>或者统一使用：</P>+ _2 ?. n8 v8 O) i0 D5 y0 ?( y/ B
<>USE DBAudit</P>9 e  ?7 I# O& s, P" m1 w
<>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>
/ [# r3 Z& k/ y" M9 M* |' _% H7 b<>注：</P>
* y5 i# r6 h8 X<>sysobjects中type字段类型解释：</P>
( i- F6 U9 }8 J<>C = CHECK 约束</P>
$ y- g- u* r2 ]1 q8 [+ i6 z/ v<>D = 默认值或 DEFAULT 约束</P>
. I/ h( X) A; X% ^9 h4 C, U: b# Q<>F = FOREIGN KEY 约束</P>; J; K  w) Y: J5 E
<>L = 日志</P>2 x* q: u; X: P3 F' i9 f3 h1 h
<>FN = 标量函数</P># f# l) x! W# k% c% ?* b0 |, h; m
<>IF = 内嵌表函数</P>5 U0 k4 D3 T% l$ W
<> = 存储过程</P>
: q, B$ R' d# q; T<>K = PRIMARY KEY 约束（类型是 K）</P>: ]  N! {9 M: x, q
<>RF = 复制筛选存储过程</P>
! U  N& ]2 u- F$ U2 [<P>S = 系统表</P>5 |$ w; v& {) j! Z1 D. B( [5 N( s
<P>TF = 表函数</P>0 f2 M8 T% X  K- p; {
<P>TR = 触发器</P>: ^4 e" b+ y6 p
<P>U = 用户表</P>' W& Q- |5 l2 k2 O) A2 m
<P>UQ = UNIQUE 约束（类型是 K）</P>
) f7 s/ t9 F% V<P>V = 视图</P>7 c, H8 m# I! n6 }( T) e
<P>X = 扩展存储过程</P>
( d: d" \, z( }' i$ O" v% H- W<P>取得列的方法8 u  x2 {5 _3 h, D) n
系统存储过程</P>
" `9 R& p7 Y' o; J<P>USE xxxDatabase</P>! P, Y& e) b( E  h
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>* U2 H7 X8 ], X
<P>或者</P>" [. Z( |+ ]$ |: W+ W( [
<P>USE xxxDatabase</P>0 a8 U% Q0 C. H
<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>
" c# d, q0 V7 k; N0 Q<P>这两种方法都可以取得包括视图的列。</P>! |6 z# H" n9 b& ~
<P>取得视图的方法- h' w* V8 t  N# {
系统存储过程</P>
4 C0 N" h3 T3 N) I<P>USE xxx</P>3 B: d, `1 i2 v6 X' ?
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>5 }: s  X5 T) ]6 b' L7 x
<P>或者</P>
! v) G. ^- w. N' y3 Q<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P>
% @9 b, ?% J' h1 Z<P>USE DBAudit</P>4 ^8 m6 i2 ]; R) ?
<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>
2 P' n- e  x9 N<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>
. k4 [( }( [3 H, i- J4 E3 H<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
, G2 r" l+ U( I  t9 g, p: n<P>取得存储过程的方法9 G: O$ G8 `  l3 U- k4 d
系统存储过程</P>
1 k9 F: p5 I5 y9 p* @9 J8 h! ~0 U: X<P>USE xxx</P>, O5 I: b6 x6 d5 O! _% a. c4 Z
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
9 [8 F8 G% G; n<P>或者</P>
- p" n' b, [% ~0 f; S8 n% o, @# h0 M<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>0 S0 _% O# {) ^7 e' c0 U2 Q
<P>USE DBAudit</P>
7 a9 i( c2 }& Q, y* J1 ?0 z/ a- Z0 _<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>
" w8 w$ e3 G$ v$ k<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>
5 a$ S  |6 D( C& f0 b5 u<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
+ h3 g- o" x) |7 Q<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>& ~4 k  E- F) _' w: R. l% q& G
<P>取得函数的方法
# s0 q' S5 }. A系统存储过程</P>
4 Y8 w2 Y5 J( w+ Q/ T* x1 _<P>USE xxx</P>
3 f$ E- ]0 }5 P+ a+ Y. [, O<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>+ x: ~2 Q/ I- T' I$ k
<P>或者</P>
  }+ S& Q% q5 J2 n3 H<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
6 o( e  n$ p6 X" G( J2 E. f<P>USE DBAudit</P>9 j9 Q" |8 F: r. f  N0 l
<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 H9 ^. U" i# Q* e% t
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P>* d. ^% l; |( n' _6 m
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>! e9 D. z# }4 ~/ [; c
<P>只能获取用户定义的函数内容。</P>
* E" j: a+ K1 T6 Z5 I4 S/ q<P>取得触发器的方法
' K3 Y6 }  u$ m系统存储过程</P>
4 y# P/ t* I/ i6 q<P>USE xxx</P>
2 G' t7 M" O- [4 e! q* R% L; i. t* e<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>
& W' F% Z% c  Z* D  x<P>或者</P>( q& q% K! S5 {
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
4 E) [- o  o: w2 _1 D<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>
/ j0 J; g# C! l7 `2 u<P>取得索引的方法0 ~3 O9 b3 X1 L% g5 L2 j
系统存储过程</P>
+ W  R3 o; I9 l6 E: [<P>USE xxx</P>
7 O2 c5 O7 a; `# \0 v: r. }  T<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>& s- C3 G: F! h( z
<P>用户
& |3 b" i8 d0 s9 m$ D: F系统存储过程</P>& m# ]  ^/ ?; B! I9 |/ s
<P>USE xxx</P>/ G0 P& A: I7 V; l! ?" A
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>( S, j- o  l. H3 Q
<P>角色
! J8 r" ?$ g8 ^5 F( v% x) a系统存储过程</P>
, ]7 @5 Y4 x5 N+ m<P>USE xxx! R5 u- X7 B1 Z5 F' i
EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>9 o9 q/ a# }- l( A! O7 l; P5 a

. t7 T* v% ?& O, Z1 g/ G, M4 z  P<P>ORACLE
! k% _% X; s( N+ t: Q4 y) ?! w特殊
$ ^5 c+ s- J& D&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>  N4 T/ ]2 R" s. _* `3 K
<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>9 x9 r" o( b- e
<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
3 r  M4 C7 c4 F8 J<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法9 @& ]/ `, T  n" p  f( c1 N2 j
只能由用户指定<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>3 s* F$ u7 [) ~4 H& j
<P>取得表的方法
  Q% J; b' ]% ?# D" q2 s/ w存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>
5 q1 k! |% M6 M+ Z# p2 j8 `<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>
1 g" e$ s+ E* y0 t7 w- |8 ]<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P># \+ y+ ^: x: \  |" v
<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>
# z% S( |2 \9 a4 K<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>
: T' y8 O; t0 O  k' P; f<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>
' m% L, i( y2 j- f0 p( J<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>4 x$ \: Z$ y/ Y$ J5 {8 Q5 I) f
<P>
0 o1 t1 `1 Q* F* g3 xDBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>! o# a4 f/ b0 T5 V8 n- v: H
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>
7 h, d$ ]1 |/ P<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>! T/ T; `% O' L, b0 J: d
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>' r9 g: k& ~5 e9 I
<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
7 x& x- X, `4 ?, a<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>! W, ?, \; g9 L( A
<P>
; m5 C- l! G' V4 N  ^因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>
1 ^9 n) V. _- _( k: E1 N<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>
/ `7 v; a) `# V, A6 j3 o<P>取得隶属于指定表空间的表：</P>
, z+ [6 i! y+ 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 WHERE TABLESPACE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'DBAUDIT';</P>; N, d: r. j4 K0 N9 H
<P>: O7 K* N$ q- {0 V
也可以不指定用户名，从而取得所有的表。</P>9 J. b% c. c2 K" ^* V3 K' 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；</P>
+ ?9 k, O* @. ^7 V) H, Q# L; r<P>取得列的方法
: K' q& g( h7 V6 z: Z1 K存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>
" o9 I+ H( O% O5 Q8 ^& h" I<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
1 w* A- `) _( y& j描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。! e& ^! U0 n4 u1 V1 L
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
* u0 j# j5 g1 D8 ^4 z# s; [描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
9 l% ?5 g" K; K3 k2 o. NUSER<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
, Q1 e) K( J; V7 d, F. M描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。
0 ^1 M1 T4 w) {9 }: H) R( eALL<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>
$ j5 r+ {. ?6 u1 N' p2 w2 r<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
( A8 Y, J4 ^3 c$ i<P>8 D5 z) V/ J. o1 }$ h8 f: c3 r- V
因此，可以从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中取得隶属于指定用户的表：+ U9 ?4 \9 c& u( @
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>
0 D7 o# o5 h1 U- @% F' F<P>取得视图的方法, o9 k; n7 m/ J' z3 y/ f
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
8 y3 |7 k% ?% |% Q1 b$ r, p) t<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS# z. F$ o) C6 {: B' m' d; x
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。
1 ~4 b9 E/ V( p, B' ~ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS, K/ K) \# J8 z
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。* b" Z* Y+ M: e5 R9 G' Z
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>8 P8 o/ D0 v4 G( d& L
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>5 @; c; \' h. T+ s% Z5 E9 ]
<P>: a8 T; v, {7 `, c( {5 V
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：8 }! l4 _! p% |: I0 a9 [4 B
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>) K8 b' N& F3 V; c' O
<P>取得存储过程的方法% G5 D6 t8 u1 ^! s4 V& z" G, W
存储系统对象的系统表/视图有：</P>5 z# ^. S! u% \& v% A: J7 E& ?5 J
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>+ `) V6 ~5 l: O1 o3 j  J+ w/ v
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>
8 {% z+ Y- `+ ~$ @; O+ x<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS1 }% I' n5 ]) a+ {; K# x" K3 d( U
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。
3 z8 k9 L0 A& Z# n1 ~  VUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
, V" H; D8 m' e6 b/ f4 C<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>) m+ d6 r( e: h- {
<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
3 f. y( U, Y) b5 c* r7 m<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>
) q4 u' g+ u$ e4 [! H0 {<P>
4 V' V" C6 d9 y; C8 L对象类型有：</P>0 F* }$ ?/ \- ^; _, M3 ~
<P>CONSUMER GROUP</P>2 ^- @5 f- S3 V7 E+ r$ I
<P>CONTEXT</P># u7 y3 K# C9 E5 U# M
<P>DIRECTORY</P>
7 q6 s2 H: w! ^( p5 ~5 M) t$ j<P>FUNCTION</P>& P1 q) x; ?% {; o+ c6 {
<P>INDEX</P>
5 W0 ]; `9 [; |: F0 ~<P>INDEX PARTITION</P>
% s. u7 D0 [' S) e<P>INDEXTYPE</P>
4 ^$ r& \, Q1 `/ n% u<P>JAVA CLASS</P>
% N" G  o2 e+ a- j" Z: B# |<P>JAVA DATA</P>
& A+ x5 a! B* r* Z<P>JAVA RESOURCE</P>
( |2 Y6 N5 l7 J1 f. L<P>JAVA SOURCE</P>/ y' k2 ^) ]+ l/ M, C
<P>LIBRARY</P>
( C2 l5 |+ C) Q! ?0 |4 F# I<P>LOB</P>
, \( T: Z) r/ F<P>MATERIALIZED VIEW</P>
' t) e2 s1 _% T6 I- ^1 h<P>OPERATOR</P>8 m" |8 `, K3 [8 h. b( m3 ~
<P>PACKAGE</P>
% `0 t7 O7 o- e/ y3 P2 f<P>PACKAGE BODY</P>
. C. V8 c9 Y5 g- Q' u" d<P>PROCEDURE</P>
6 i  [/ e/ }( C$ h<P>QUEUE</P># I3 G4 K0 D% Y5 Q, m* C; L8 |, T
<P>SEQUENCE</P>5 M" f* U, a( M/ ?
<P>SYNONYM</P>
7 |; ]3 B$ a- A2 d! I, a<P>TABLE</P>. L0 l( @- e. L! w5 U* x& z
<P>TABLE PARTITION</P>6 s6 @" l/ ]# K; Z
<P>TRIGGER</P>
9 o  I5 H% g' ~1 _; j<P>TYPE</P>) ?" I7 c! a/ H, I' ?6 K/ W5 I5 T
<P>TYPE BODY</P>% N0 H4 Z. ^* }9 v- t& b
<P>VIEW</P>& q% P  b1 n& J& x* b" K4 ~# v+ G% P6 y
<P>
5 Y9 r& i) y% G1 T* m3 N, [* `因此，取得存储过程可以用：</P>
7 `! z( Q. W! `7 A<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>2 D/ F' p* W3 O1 C. W, A
<P>取得隶属于某个用户的存储过程可以用：</P>
$ z- L! J4 o# `4 G; i: F<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>( }: d! d  T$ D: |
<P>9 s0 G9 t$ a" H# q. B! ?
同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>
- [% x- L3 H2 i* e( ~6 I<P>取得存储过程内容的方法' s( Z/ ]8 b! R5 L
对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>
9 Q  p& F* k- F0 s' w2 T4 s8 l0 V<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
9 B* N0 n9 C" S3 G1 ~<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>5 ~: c5 q! D6 u. ?0 r! \
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE
5 d  x' X' ^' Z1 R/ E存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。
4 }2 X4 T/ n  R4 b. f  z7 {USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>8 e0 `3 K) g3 p0 T) t
<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>/ V3 I; Z3 x: b) g7 J7 X, {
<P>: F7 e  P7 f' G1 F2 J! N* y& f
因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>% \  g. r) N8 J
<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>
8 a0 m- p$ |9 J4 ~4 e<P>取得函数的方法
; k) C: ~" B: h2 F同上，取得函数可以用：</P>
5 u. U& i* F2 \- j' 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 = 'FUNCTION';</P>* @3 \5 S5 F$ e- k. n
<P>取得隶属于某个用户的函数可以用：</P>8 _* P1 q9 \! K7 o2 `
<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>1 T& U3 L7 e7 ]- ^5 ^
<P>% d- |. J$ m  I8 u- U% H
取得函数内容可以用：</P>0 Y; D  {+ u% I7 u1 _
<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>$ R) ^6 c& }( }2 S, t
<P>取得触发器的方法
4 C7 f5 V2 V3 G  g存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
  G) T2 E' s; B2 S<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS( E" b. F7 d+ D; s1 B$ ^8 q, o- c+ o, W
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
' N6 O0 K3 o4 r. g% Q. n4 ~5 JALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
8 C8 h8 J  \$ P0 }! [描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。
; U3 y2 A; f% p1 gUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>: S3 [2 ~& J. E5 z3 w4 X4 |. _
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>
- Y' x$ f0 z+ K# B<P>( I3 O9 ]  K& Q, H4 o
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
) D+ D& @3 E! ESELECT 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>& ]. F0 x4 ~  ]6 m# i3 M4 Z
<P>
- i  @: o0 O9 Z. m( z! j取得触发器内容的方法：</P>( o' c6 V2 z% D! h0 K9 U* ]
<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>7 W7 Y: X7 i: t; _4 |
<P>取得索引的方法8 R/ I# ]* z$ K' @1 p* B
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>
5 s# S6 O/ r. l% G- V<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES2 _" O" {8 O$ s$ h0 F
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
& S2 K% d; Y$ k  }ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES
9 r$ A# D4 l- t; u6 s# n描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。
, p2 Z9 E$ v! j1 uUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>1 O! ~$ i- t( R7 Q% @  j9 t
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>3 k1 D# h* p4 S' U7 m% k5 B. n" y! T9 y
<P>
6 P4 G9 G6 [7 h; b3 C! {因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：
4 D6 {1 l6 P+ |0 F$ ESELECT 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>, T& _5 Q1 W2 r& [0 n3 r
<P>, h' |3 N3 ?9 |9 f" B2 G. m
取得索引相关的列的方法：</P>
% D& N8 {; Z" ^2 g+ p<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>6 n5 \2 n. M5 }  ~
<P>用户
+ B, e- _3 T% B存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>
! D( J$ f! H8 @1 l<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS; U( u6 e3 a, ^$ o5 ^) ]
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。' n" y% G# K, e
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS% [* s7 K! N, N$ ?& u
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。$ P! j# e! M* C0 Q3 q1 [- H  N
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>
; w. U7 I( O) g' `<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
* T/ m6 _* H3 h) @, U7 s- v<P>
  _  `& N7 \1 }2 W因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：6 d) s! _" H$ }" K
SELECT 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