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

韩冰

<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象
) Z- A9 [4 D- F6 c5 e" K原作者姓名 Fang</FONT> </P>
<>SQL SERVER
取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>
$ ?0 O4 O6 N7 A0 Y3 }! T5 I<>或者</P>
<>USE master</P>
$ m1 [6 R1 j) i) A<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>
3 O) l8 j3 D  S$ Y) N6 {<>
. ?4 Q. N4 r1 K( f1 m- R7 esp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>
; L2 {' ?6 Y) X; }  ?9 A3 V* `# \<>取得表的方法
系统存储过程</P>
9 ?/ q, |+ S/ R" J0 E2 h<>USE xxx</P>
' C! g' R( {2 A# k<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
<>或者</P>
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
<>USE DBAudit
, v# l% b# f* U" s5 _SELECT 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>
<>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>
8 F* d4 k; d/ Q3 Z<>或者统一使用：</P>
<>USE DBAudit</P>
& J: T4 `9 V3 f+ n4 ]. s  i<>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>
/ U8 _7 V0 Y: o) P+ Q) s<>注：</P>
<>sysobjects中type字段类型解释：</P>
<>C = CHECK 约束</P>
<>D = 默认值或 DEFAULT 约束</P>
% J% ]  h6 _! c, G8 U<>F = FOREIGN KEY 约束</P>
<>L = 日志</P>
8 W1 Y+ A& F! @<>FN = 标量函数</P>
<>IF = 内嵌表函数</P>
! a) R8 A8 G& j( n6 R% Z- j<> = 存储过程</P>
<>K = PRIMARY KEY 约束（类型是 K）</P>
<>RF = 复制筛选存储过程</P>
, j& [% ]) Q) w" W<P>S = 系统表</P>
<P>TF = 表函数</P>
<P>TR = 触发器</P>
; N1 z, s: S1 _- f) D<P>U = 用户表</P>
9 H8 X( q. b/ h<P>UQ = UNIQUE 约束（类型是 K）</P>
<P>V = 视图</P>
<P>X = 扩展存储过程</P>
9 I$ Z' k- M% D$ d9 E<P>取得列的方法
系统存储过程</P>
4 d0 A4 g7 z; U7 }7 @9 p7 w; {<P>USE xxxDatabase</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>
<P>或者</P>
<P>USE xxxDatabase</P>
# C0 w- D4 w: @<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>
3 y' W  M/ y) ?+ w* [+ [1 Z<P>这两种方法都可以取得包括视图的列。</P>
" [* @  E6 ~6 j<P>取得视图的方法
( ?8 j# v3 K3 f5 m  E2 c& A, @4 Q! e系统存储过程</P>
1 W4 e  q. T' ^<P>USE xxx</P>
" T# r$ F: n: h. n1 a- A* V<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
<P>或者</P>
3 g/ X3 R, H) p! ?/ r<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 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>
3 Q$ w. Y" o0 Z<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
; r3 H' y" ^  B: z8 X; v<P>取得存储过程的方法
9 a! l& ]7 V# I; g, p* d2 W系统存储过程</P>
<P>USE xxx</P>
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
<P>或者</P>
+ c- ^+ r$ @, o% f2 R; C+ ]. E<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 = 'P' OR type = 'X'</P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>
9 i1 a* k- q0 i  N# N<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
2 z1 z# e) M4 V2 ?6 C* ]+ x<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>
<P>取得函数的方法
系统存储过程</P>
<P>USE xxx</P>
/ _2 ^, B! ~- v. M/ n* E1 k$ R<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
9 ]; R# H2 @3 R! g  `! y<P>或者</P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
<P>USE DBAudit</P>
& y& p# p  @4 d* A( H1 c<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>
<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>
  l9 E" S& u3 z2 G<P>只能获取用户定义的函数内容。</P>
- h) C6 s2 _& y% E<P>取得触发器的方法
9 C6 P" X& E% W) _& W- T( F2 a8 Z3 z系统存储过程</P>
<P>USE xxx</P>
. \. q" a  h' p& }- |; L. d<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>
6 |( e' q6 G% w, f<P>或者</P>
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
# e* u6 H9 V: v<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>
<P>USE xxx</P>
( W7 K# d2 V& d% u" [<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>
8 z" @; J3 x; T% w7 f; O<P>用户
! w2 H9 u1 C7 E! X) R$ E系统存储过程</P>
' f0 v; B+ d" W) L<P>USE xxx</P>
1 {7 d+ H% e; B3 I3 e<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>
1 ^( K. m# |' s; M8 R4 x<P>角色
' B8 B( m% L$ ?1 B. k8 @2 J系统存储过程</P>
$ g* h  X) _- h9 P<P>USE xxx
+ G" {% m: L6 E' \( `2 @* a+ [  I, FEXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>
2 @# b5 k, h0 V6 L7 ]+ X
1 O3 Q9 |4 Q) u<P>ORACLE
4 u0 z# w: u9 L3 O% u特殊
$ A1 S3 }! ~: [3 x) m! V! P$ {&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>
<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
; b6 P" f; W3 O( E只能由用户指定<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>
% E  @, v4 g5 `  e) {<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>
<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>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>
: U) C' a. e( \1 _4 f" p6 U: Q6 e7 @<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>
<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> TABLES</P>
3 s9 g, G6 t& Q" D<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>
* D$ }" m! t1 A' `0 k5 d% F<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>
" [  D' J3 h# h& m! C! W<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
1 E8 D5 ]' A) `$ [<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>
! V, u! G! [6 }* x  m; F! `<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>
<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>
1 ]' d  B; U, l! Q<P>
+ p8 t! x" U; I也可以不指定用户名，从而取得所有的表。</P>
! H  I$ v+ }4 t* L! X* 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>
7 ^4 R1 g0 W) B2 i$ h, ?3 @4 C<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>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS
描述<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>COLUMNS
  E' I! c  B* U/ C$ x* p/ ~描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
3 g' u6 \) o$ h/ o( S" m, PUSER<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>中当前用户拥有的表的列属性。
: K8 {& S3 a( _) ?; b+ 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>
8 ]! v7 k+ F, R  o0 ~. a<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
' ]# R! e$ M4 N0 G<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中取得隶属于指定用户的表：
% W5 f4 p8 L+ u* ~& t2 WSELECT 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>
3 `0 G' V( w/ q! d1 q7 n5 W<P>取得视图的方法
& |& I1 [( q: w8 G/ A; `存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
7 e! L% r$ c; ^2 `<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
+ `, N  D' M3 n1 h. Z7 F) R描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。
. p( Z/ _! Z) G! U; ^4 KALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
% i2 v9 ?, K# y3 _+ i  }描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。
( z+ B/ l5 N4 ~3 T0 q3 s& O  B+ W9 bUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
7 E* O/ Q8 V. X$ a0 t6 o<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>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>
$ t! u; V1 |( u# Q6 K<P>取得存储过程的方法
) q# k' `0 H7 O$ c; a9 M3 i$ g存储系统对象的系统表/视图有：</P>
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
4 z7 t7 I# i4 T& ], _- `<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>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>
1 \+ g0 y( _6 w6 p<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>
<P>
对象类型有：</P>
5 h7 m1 y  n; q: }  @% X3 {0 k8 t<P>CONSUMER GROUP</P>
<P>CONTEXT</P>
: ~6 ]: K0 g" u8 @# a3 B% a<P>DIRECTORY</P>
<P>FUNCTION</P>
7 D( @8 u/ {$ E9 ?4 k<P>INDEX</P>
<P>INDEX PARTITION</P>
$ E0 T) Y+ p) W<P>INDEXTYPE</P>
<P>JAVA CLASS</P>
- V- G+ P; W! c5 [; p9 h6 H<P>JAVA DATA</P>
3 i( v  \' N  H% E+ S; \<P>JAVA RESOURCE</P>
<P>JAVA SOURCE</P>
<P>LIBRARY</P>
<P>LOB</P>
<P>MATERIALIZED VIEW</P>
+ m  e0 ~, u+ ]2 X' c+ f<P>OPERATOR</P>
<P>PACKAGE</P>
/ R# y( C# k: M: m" m$ @% E<P>PACKAGE BODY</P>
<P>PROCEDURE</P>
<P>QUEUE</P>
% s: [/ W3 z( N; P" z) a<P>SEQUENCE</P>
<P>SYNONYM</P>
4 e+ x# Q: i9 r+ F; e" u5 k<P>TABLE</P>
<P>TABLE PARTITION</P>
' z0 n' ~) Q5 `; {<P>TRIGGER</P>
<P>TYPE</P>
! I2 V# Y( o- Z6 R, c  {# ?& i3 [3 [<P>TYPE BODY</P>
+ B! R3 E( s' u+ m3 r) e& q; P<P>VIEW</P>
! D# _) F& h' u1 S0 u<P>
# d9 |( Q2 A1 d3 l  Q" s因此，取得存储过程可以用：</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 = 'PROCEDURE';</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 = 'PROCEDURE' AND OWNER = ‘DBAUDIT';</P>
<P>
同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>
<P>取得存储过程内容的方法
2 j# d! g; y. D2 z4 b1 |对象类型为类型、类型体、过程、函数、包、包体，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>
<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
+ B: {0 ?6 I) q/ ~9 B, H2 x* F<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>。
USER<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>
& ?$ p8 F* b% f* ~) p$ L<P>
因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</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 = ‘PROCEDURE';</P>
& j" I6 ]7 {: u, y<P>取得函数的方法
( d5 _: I) h* ^/ X/ `- z同上，取得函数可以用：</P>
% G9 S, t% k5 L" u2 a7 O<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>
<P>
; F0 i. ^6 x+ l& s/ F" _1 e取得函数内容可以用：</P>
" R: n9 E6 h5 y% K& E/ T1 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 = 'FUNCTION';</P>
2 @3 `1 R! r$ W<P>取得触发器的方法
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
4 ~0 a7 _3 P2 a1 r5 k6 F<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
: I% ^2 q. z9 p  s描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。
: J7 J! T( X/ Q( V- e5 nUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>
" H1 F/ \% _/ s" G0 ~2 I<P>
- ^* ?% M8 }' F7 l& _& l因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
: _: k- \1 X- Z3 }0 y! @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>
" u3 M! K5 u& Y$ c& S7 q) a<P>
取得触发器内容的方法：</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>
+ z1 v8 ]% K/ b. z* ~; F<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>INDEXES
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES
) H7 j( e- E8 n4 ]: {: j2 n描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>
" ?' X! K- Q4 }9 H& o' h  i1 R* g<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>
( v" ]; i0 D" C: ~/ N% v; c: y<P>
& f* e) t' r, s5 o- w因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：
, m5 h& z6 S) z' ]2 r7 MSELECT 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>
  `1 `: \: H4 a5 V1 l) i<P>
取得索引相关的列的方法：</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>
<P>用户
0 I0 J3 A/ _' {, F! |3 [, D存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
0 f1 K5 M, E, @: rALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
: Z3 a8 e5 c; n9 s7 t描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
5 V! Z0 A- _/ b* O, KUSER<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>
- }5 w# S0 Z( ~& E. T" K- t<P>
  j' U- l. S" }5 m因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
5 T! \+ l) G# Q* C. U9 w* Y9 [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>