获得数据库对象的方法探讨

韩冰

<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象
原作者姓名 Fang</FONT> </P>
<>SQL SERVER
取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
/ q- q9 g- b. p' Y系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>
5 l* B! C3 n/ W( m<>或者</P>
2 d4 M( `& D4 v9 T<>USE master</P>
2 i7 m4 \6 _7 ^3 q5 i6 W0 P6 d4 k<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>
& n7 b$ H9 |$ k5 y* f+ I" f6 l8 o) `<>
9 M' [3 _1 \& m9 }8 G; rsp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>
9 \+ C$ z6 V$ v( [: L# D<>取得表的方法
系统存储过程</P>
<>USE xxx</P>
$ x' ^" `# S( M7 L3 h$ q7 n<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
) k/ l+ }7 c, J' L) Q<>或者</P>
0 z. f& i4 `- D! V3 T+ ]<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
<>USE DBAudit
" ~8 k5 l% Z- I# T+ K& gSELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>
# z' p* P5 l7 L* F8 T+ p0 i6 p<>USE DBAudit
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
: i# Z) A+ a; N5 n3 u! B& P# F<>或者统一使用：</P>
<>USE DBAudit</P>
<>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>
<>注：</P>
% n# ]3 `) Y  _! X<>sysobjects中type字段类型解释：</P>
% F" m4 [/ K4 F! j<>C = CHECK 约束</P>
<>D = 默认值或 DEFAULT 约束</P>
- T# g9 m9 G6 j( @6 g+ s1 ?<>F = FOREIGN KEY 约束</P>
<>L = 日志</P>
<>FN = 标量函数</P>
6 U( A: K6 r9 h/ x<>IF = 内嵌表函数</P>
" l6 E; y9 e) X0 b& N5 p<> = 存储过程</P>
+ H+ e. v1 \& B5 ?7 a* U. A<>K = PRIMARY KEY 约束（类型是 K）</P>
# D4 m8 @; i; \) e% j: c' }<>RF = 复制筛选存储过程</P>
<P>S = 系统表</P>
; d' X# N$ w; H3 `2 o  ?<P>TF = 表函数</P>
# T4 Z! K, q! @<P>TR = 触发器</P>
<P>U = 用户表</P>
9 T% I. G: H4 o  Z- @<P>UQ = UNIQUE 约束（类型是 K）</P>
( n6 R* U$ |; R. v8 R, c9 M3 g<P>V = 视图</P>
: ~4 [1 R  G* F) A+ r  T<P>X = 扩展存储过程</P>
& T% M8 {# B6 f1 k: i8 e4 ?/ ^<P>取得列的方法
系统存储过程</P>
<P>USE xxxDatabase</P>
( W. \& ~4 y* D. M0 o7 S<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>
8 ~( y5 K: j& _/ S" b. O& Z<P>或者</P>
<P>USE xxxDatabase</P>
3 G2 C9 w* P  I8 A& U5 m) i8 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>
<P>这两种方法都可以取得包括视图的列。</P>
$ B" }, O" J* R! e6 F1 N' q1 E8 y<P>取得视图的方法
- B* @" U  k8 E5 @9 m. I: M系统存储过程</P>
3 R5 \3 o8 _: P4 o( K/ z<P>USE xxx</P>
* ~/ a; R) }% A7 U" d; j5 ?<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
" @- W9 J3 j, O+ C, U<P>或者</P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P>
<P>USE DBAudit</P>
7 |$ E/ `2 G% `9 y& i8 p9 }& [: p<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>
2 r. e: w9 V* ]$ O6 t1 B<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
<P>取得存储过程的方法
系统存储过程</P>
<P>USE xxx</P>
4 o! n, p- s! a& a9 C- k* E% n; }<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
2 x, }4 \  L; E" L<P>或者</P>
% ~) [1 B8 F7 }9 A% ^<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>
<P>USE DBAudit</P>
) V) n* d! @8 ^' i' M<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>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
" x: s7 P" g" @, H1 T* I<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>
<P>取得函数的方法
3 R) c  g4 L! G' {系统存储过程</P>
+ G4 E4 F6 \0 t<P>USE xxx</P>
: K6 V  n. c1 a4 H  O( N& s, S( O<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
- [" x  a1 l) a<P>或者</P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
<P>USE DBAudit</P>
<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>
7 b' e. ~. G! |<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
: Q% H' E  G8 J( y. H7 S<P>只能获取用户定义的函数内容。</P>
  A7 h6 b- t5 M<P>取得触发器的方法
系统存储过程</P>
8 O1 p1 a2 C) |<P>USE xxx</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>
' ]2 s5 k7 Q  @<P>或者</P>
' a* M0 ^5 e8 f7 Q( }' x<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
8 Y% _( S; @. d1 O+ F2 @<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>
<P>取得索引的方法
系统存储过程</P>
- r0 o( S( G- N' ]9 B<P>USE xxx</P>
  f( N9 y/ s+ a6 n<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>
<P>用户
系统存储过程</P>
, u) R2 V( Z* `* n4 K<P>USE xxx</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>
/ T5 w" h: c. H1 d- Q  O+ T8 V& S3 u<P>角色
' J5 \% t* M9 R" `/ @系统存储过程</P>
<P>USE xxx
EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>
+ s6 I2 f7 i! o; R" N5 v. W
0 S, |7 J2 B. j2 ~+ O<P>ORACLE
" u; O! A6 z% K  \( L( r( e" Q特殊
; }! h  Z# v# V4 z) i&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
* t2 b! f# l. D6 c<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>
8 W1 T2 j4 c7 B<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</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>
, B+ n- y7 D2 J( V<P>取得表的方法
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>
<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>
) w4 ], w. ^7 q9 P; W0 s2 [  O4 E<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P>
: e& L) t. U; V8 }; a<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>
  r7 D4 y& M9 m4 L2 e$ d* S8 D<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>
<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>
3 w: l! _. S) Q6 z/ Y<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>
9 G& i" x# ?4 k7 O& x/ [* ~<P>
+ V: B/ p* z# s8 hDBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
& C% X5 `! j* p<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>
<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>
& D/ B+ N2 ]3 D<P>
" d8 p8 q) w- {" p9 |+ y因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>
# I( S# ], a$ l3 }<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>
" C- Z" X2 M) d7 F9 [: t<P>取得隶属于指定表空间的表：</P>
<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>
<P>
6 D6 O8 R5 W0 R6 k! P6 _9 _也可以不指定用户名，从而取得所有的表。</P>
<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>
<P>取得列的方法
& g2 `2 w, N( ^! Q. C存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>
<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
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。
' D3 o2 h) W% t* Z' e+ c3 o' GALL<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
" j7 t5 R( h0 Q3 U0 o, D. n描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
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
6 n, N4 ?% E) T$ z6 d3 b" B描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。
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>
/ v8 g3 r! m( ^<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
/ A3 d2 |/ c$ s& L9 i<P>
因此，可以从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中取得隶属于指定用户的表：
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>
* ~$ b. V/ @$ D( c5 |% j<P>取得视图的方法
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
7 _& E* V! ]6 O, x& g# I8 I' `<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
7 j: r- {. T( C; m/ r: S2 |描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。
  {) S9 m7 K8 d* iALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
0 K: X% ]  z6 D描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
; v' y( b" E- V6 ?; Y) O  r<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>
<P>
- ^& x% L1 Q' X/ }9 ~因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：
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>
<P>取得存储过程的方法
, x* w+ L" _; @存储系统对象的系统表/视图有：</P>
: h6 S( T7 i" O<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
2 i; a* h- J+ w  T  _<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>
$ C) p8 @- U3 \! c<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>
<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>
<P>
对象类型有：</P>
$ O: r. [- {9 A1 k4 |( c<P>CONSUMER GROUP</P>
: w- L/ q4 g2 h% ]7 D<P>CONTEXT</P>
<P>DIRECTORY</P>
& Q6 D- t9 ~- W  B. }<P>FUNCTION</P>
<P>INDEX</P>
( e, `: y. _; c6 ~# B. E<P>INDEX PARTITION</P>
$ T$ q9 d" g+ g; C<P>INDEXTYPE</P>
<P>JAVA CLASS</P>
<P>JAVA DATA</P>
% d& o" x" U0 r3 r1 ^, [<P>JAVA RESOURCE</P>
<P>JAVA SOURCE</P>
6 s9 N- _, B$ G& X9 z6 \<P>LIBRARY</P>
  [3 q# \$ I+ y7 K- V<P>LOB</P>
0 N( ?9 N+ ~" c5 ~9 Z<P>MATERIALIZED VIEW</P>
; m7 Y1 F* o) e, d' e) D. [<P>OPERATOR</P>
" f- d- G1 a3 A! M7 {& y<P>PACKAGE</P>
<P>PACKAGE BODY</P>
- V& z+ e0 |5 y* K<P>PROCEDURE</P>
- q* V" M* Z% C! X; D) }5 F: T<P>QUEUE</P>
2 {* a1 S* l+ ^& S7 p<P>SEQUENCE</P>
/ E& h" F1 T: }<P>SYNONYM</P>
<P>TABLE</P>
, T) a3 g' N: d<P>TABLE PARTITION</P>
<P>TRIGGER</P>
<P>TYPE</P>
8 `& o$ J+ h: T6 }8 D9 Q" y<P>TYPE BODY</P>
, {0 M" H# L' S7 H! s% r<P>VIEW</P>
<P>
  j8 A3 T: J& J' f  W因此，取得存储过程可以用：</P>
9 X, x" J5 Z  r' `3 c<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>
<P>取得隶属于某个用户的存储过程可以用：</P>
% {1 n7 |6 a% h7 {<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>
8 X+ ^( m6 _; ]<P>
同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>
3 x* ^; S: n; T% ]! m. K4 T<P>取得存储过程内容的方法
对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
# ]- C) U" X' J  x/ W) b<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE
存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。
# \* F$ Z5 |' j  @$ f5 ~" i3 JUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
# m4 v6 D8 g, b, r<P>
因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>
" \% A) _2 o- f( `/ v<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>
: r6 T( n+ F: r" s/ _<P>取得函数的方法
7 _5 ?, }& _+ Q3 `同上，取得函数可以用：</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';</P>
<P>取得隶属于某个用户的函数可以用：</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>
8 M1 r* [0 J3 ~<P>
取得函数内容可以用：</P>
<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>
* z  Z; |# u, m/ g* }- T8 T4 U<P>取得触发器的方法
% y, j6 @; j- x$ Y7 \( |存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
3 }1 Y2 @" X3 U+ |" g6 C* N描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
' O9 s. K: h* \4 ]/ M' d& yALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。
# [* z* T1 E$ W1 u; Y* j7 i5 r) }& \USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>
* i" ~2 h% U: E+ C! D<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>
3 X5 p4 ~# n7 y% ^4 C2 m<P>
' N( l3 @+ b1 s# @% ]因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
2 l" g( @5 L  @, I! G5 L( mSELECT 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>
4 X3 p( `4 M6 A3 d9 }<P>
; K9 k- ]# u3 y0 z" M取得触发器内容的方法：</P>
<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>
; ^, {0 G' L8 y) r$ N/ W<P>取得索引的方法
; Z" f. Z3 G$ q. n5 i$ P$ A, l存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>
* Y' r" h3 l/ a7 j<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES
7 F# w* e* d2 }7 f6 v/ Q6 Q' F描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES
& L3 z1 u3 z0 X. b描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。
' {4 R# S4 |# g2 H* aUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>
' A  [1 p, G3 V<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>
: x' E" S6 i. O! Q; o<P>
! {: _4 y+ c! S: e因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：
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>
- q+ e9 F- S, x5 q  `4 `3 f8 v<P>
6 c* C/ U9 m, o4 s/ q取得索引相关的列的方法：</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>
$ w  W0 A, X5 e2 V6 t6 G<P>用户
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>
& x  v7 @) T  C) f& G: {<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
5 T+ |  y( [8 `描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
8 w2 f; {; W8 T# \描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
<P>
/ Q' Z9 b2 @% u( j  H* f因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
1 R0 I0 G( f6 e+ U  j  {5 BSELECT 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>