数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]
作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象
! i: e: G% H: D原作者姓名 Fang</FONT> </P>
! q- A9 q; T* x/ V, P<>SQL SERVER
) M0 m3 ~( e1 G取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
5 r" J1 Q. g6 U0 V0 |6 \" D  S5 R系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>0 A2 v! e1 M+ ^. @0 I
<>或者</P>( h8 \- l4 `8 h. \) A1 {5 A$ b
<>USE master</P>8 L( k& F2 y8 H' A  [* R9 f
<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>; S8 D( ~6 n% r) Q! ~
<>
, @& }6 B; l  q  K; Usp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>0 B5 H8 Y6 {+ O7 h: {9 h; j. B! C# V
<>取得表的方法+ J6 @: z& |0 V( P
系统存储过程</P>
' t6 f: S+ y" q7 u" N) V<>USE xxx</P>
1 j6 w# X8 ]) F<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>9 A$ M' T) N/ ^/ p
<>或者</P>7 A9 W; z; J9 M% n9 P. H2 X& Q
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>, ~; x' c( E% ^
<>USE DBAudit4 Z! K9 m& }8 B1 Y
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>- N' K7 C  E. D' X: L! K
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>
+ v% D$ j. H: g/ \- v<>USE DBAudit
2 R, m1 u1 S; F9 l( v4 R! ySELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>4 d, ?, K+ t* D
<>或者统一使用：</P>8 Y$ I. U$ j  q8 K( K3 V9 `$ l
<>USE DBAudit</P>. K- V8 j# j/ A0 R: ^) p% W. n& R7 r
<>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>% ^% R7 [$ K* M2 ?9 N3 d
<>注：</P>1 g/ @, r3 G, y9 F3 N& m
<>sysobjects中type字段类型解释：</P>* F$ {6 L6 l  r3 }( {' c
<>C = CHECK 约束</P>
' ]- m5 X3 \3 V9 `8 @7 Q<>D = 默认值或 DEFAULT 约束</P>7 L& M6 M0 v; j% ?5 g  h5 P0 T
<>F = FOREIGN KEY 约束</P>
6 a4 F. i5 ?' \' A6 Y/ Z& @<>L = 日志</P>
. D# B) q+ S  w" w2 s<>FN = 标量函数</P>
6 z, O5 H; h6 b* W3 d<>IF = 内嵌表函数</P>
& Q6 Z6 n4 H$ i- t9 H" L<> = 存储过程</P>$ {, F4 u; l  b7 B: [
<>K = PRIMARY KEY 约束（类型是 K）</P>' S% d! Y  g7 ]3 p+ O8 A
<>RF = 复制筛选存储过程</P>9 l/ ]  Z5 V. b* @
<P>S = 系统表</P>
  C. t  [# A8 ~6 t- X<P>TF = 表函数</P>
5 s$ E& C" x+ u<P>TR = 触发器</P>
; \! p5 [. c! c) \3 p! g$ v<P>U = 用户表</P>" K' \7 M5 T6 [/ [+ \$ F
<P>UQ = UNIQUE 约束（类型是 K）</P>7 ?1 I+ H1 a: @( k3 {" [
<P>V = 视图</P>
5 Y( p, f& B+ Z# P% s% B$ _7 ]<P>X = 扩展存储过程</P>
, B) w8 f5 y! l9 p' M+ b<P>取得列的方法& l8 f5 `2 ]5 N$ T
系统存储过程</P>( a1 V6 E3 a) w
<P>USE xxxDatabase</P>
0 Q$ ^6 c8 H0 G/ V" u$ p<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>
) F7 }. S9 `  W- b<P>或者</P>3 G$ `% M2 i- a
<P>USE xxxDatabase</P>4 ]+ z) v  ?1 D8 f% A. R$ j
<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>
" H( p6 Z, n7 Z, @8 d& z5 v<P>这两种方法都可以取得包括视图的列。</P>
2 O! M1 z5 |( @* k<P>取得视图的方法
1 s0 X( S: M! \; R" H系统存储过程</P>! K; I1 x# u4 w/ a% E- [& M( O
<P>USE xxx</P>
$ S) W: a" I; Q2 w! A9 J9 _<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
' T) k/ H  Q9 i$ B5 N<P>或者</P>
$ y5 [+ a3 P, p% \) [<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P>& c# a* e4 a# n; K
<P>USE DBAudit</P>5 b1 W* H4 s: f0 c3 E
<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>
8 r$ p( a/ _% R3 Z% t' W6 A8 D8 G<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>% W7 b6 I& ?4 V' I
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
/ s9 g% o+ t4 [" l7 m<P>取得存储过程的方法) P8 ^: W/ r# g8 K5 Q1 n
系统存储过程</P># L' |6 k+ ^! f3 Y
<P>USE xxx</P>' d7 U7 B) V3 f" [/ \
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>8 V7 q% E- C# e6 O0 f
<P>或者</P>( C! D" T8 r5 S# Z; J. q8 g% d0 u
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>
& x# w  p* o" m6 ^; h<P>USE DBAudit</P>
  `: v. B) n9 _2 L3 R/ B<P>SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'P' OR type = 'X'</P>1 x5 @) \9 Z" \% ]
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>! W: ~- @5 d7 D# J& I  o  }/ x
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>1 t5 S/ J% \4 A% k; y5 x
<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>
2 Y1 W# u+ B# D% ^: f<P>取得函数的方法
: K' x# B5 F- k3 x. y9 f系统存储过程</P>
% y% R. T- d- [<P>USE xxx</P>3 U- y( f; `4 J+ c
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>1 c. U8 ~- L& p3 {1 V+ d5 H
<P>或者</P>
. R' G8 z5 U9 p3 @7 Z<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
1 o/ |( r- t0 b1 a. R3 ?<P>USE DBAudit</P>8 q0 C  l2 d7 d8 v  C' b
<P>SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'FN' OR type = 'IF' OR type = ‘TF'</P>
& Y9 I: a7 ]3 N<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P>$ l1 Y' f0 ~$ {4 X/ T9 S
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P># A; O2 K2 P/ Z9 Y, y
<P>只能获取用户定义的函数内容。</P>7 ?, Q. O$ r  R5 N
<P>取得触发器的方法
: x8 q# q. w3 T" G! b& s系统存储过程</P>
6 U" L* i2 c4 {9 f7 `4 w<P>USE xxx</P>
9 T) z5 o1 X. v8 P/ X3 E; o& E<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>
2 g7 a7 I& {! H<P>或者</P>: d6 W8 L. b5 t% b& C- g
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>
4 V3 d) L0 e! e! k<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>
. C1 X3 b  ?5 _( u# C/ k<P>取得索引的方法
8 C9 @7 F6 y, S* r7 m系统存储过程</P>
! r) ?- P1 z8 p' [  u<P>USE xxx</P>
" ^6 u8 {; k/ R- F& N<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>
4 X3 m0 S/ k" I! q. k<P>用户  X& c8 Z' u: \+ u3 {5 `( ^
系统存储过程</P>
$ C0 k7 `! |8 [- k<P>USE xxx</P>7 m9 U5 w) D) I' o1 A* V2 G7 g) r- x) k
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>
' C" N, T; w4 {9 p<P>角色9 b* @- ~5 F6 s* ~
系统存储过程</P>
! P* C0 r8 M( ^4 h<P>USE xxx
2 w. A& p$ J$ f6 k% b3 R: mEXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>" o. L0 @5 j8 D, m8 d/ E

0 n3 g* j/ r/ }& s! a# U1 \<P>ORACLE
* J; ^6 U. j# j6 [特殊: G- [; Z/ Q1 d7 N5 n6 |
&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
, d# ?& L8 F5 h+ S9 j8 E2 G<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>5 L7 {3 F, u" B7 Q! X4 |
<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>
. S  z5 Z/ Y$ W+ B2 _  x5 g+ `<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
* @  ]: g- h7 }  b只能由用户指定<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>% l, g4 `2 F8 G. ~+ _2 x
<P>取得表的方法6 V5 g; _6 K" v, x6 a% h
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>6 c1 ~% ]! O% F8 H$ `/ i8 R9 S$ ^4 T
<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>
3 j. m) n# P% G* v. B<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P>3 A! k# R' Q9 o8 \
<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>
% q& D% t1 s5 j3 H7 [9 A1 [' Q<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>
1 N( _* V. ?: r& t# y, r# d: 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>' \' l3 O& A& {2 d* p9 w6 Q
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>" ^% |/ k5 G; }
<P>
& X2 `! O9 b  R$ p; A; b" }! @DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
# @& g) {: J- A% P0 d! s<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>" D; m" w* I% O# N
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>; u9 i* m  b9 K
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>; Q% S1 o) p) k9 s) P% `
<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
& H4 G6 U( }8 R0 f* k1 Z<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>. x" @7 Q! E* W# h  Y
<P>
1 h6 W* i3 }. n) M' }因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>
3 Z& y7 R- S( r3 u6 O<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>, F; Z9 R! l  ~" x; r* Y+ e
<P>取得隶属于指定表空间的表：</P>
4 r  _1 ^9 ?: i<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>5 p1 d1 L" _6 b3 ~6 c
<P>
1 b! u2 ~* D) o# l: {2 Y也可以不指定用户名，从而取得所有的表。</P>
1 d0 j, Y: Q( Z7 z- C<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>
! g  b2 J- H6 R7 X# u<P>取得列的方法2 ^- Y8 @. p" T7 I% W8 J3 l: m) W9 B
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>! {8 l+ h. V) ^, `( G% G
<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
, g) d8 N6 u3 c0 ~  Z描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。1 i( W+ G9 t2 `: n1 |# f0 i3 N
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& D) H1 Q% W% O) g6 t. F& J/ }- Y
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
: d2 f+ d+ c! {6 L/ y5 IUSER<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+ S& u% `" d1 Y% _8 c4 o5 r
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。8 B  w4 j# H9 G0 l) K* R
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>
3 ]% @4 K& {: _! x6 c. v8 ]2 C) G<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
  r- E6 b  V7 @5 @<P>  k( I( z2 a8 y' E
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS中取得隶属于指定用户的表：1 d! i1 W, P$ w3 _- f" K7 m
SELECT COLUMN<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME FROM SYS.ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TAB<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>COLUMNS WHERE TABLE<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>NAME = 'TASK' AND OWNER = 'DBAUDIT';</P>
& K# z. k  a3 A& F5 y<P>取得视图的方法
( }) W% Y/ Y3 \# M( j' \存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
* u' B& n9 Y+ P5 e<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS0 f  ^# r( Z. \, L# \8 f
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。2 Q( f7 x+ F. A
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS1 {! P/ a' J$ Q( T. ?8 h$ c
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。: S/ w2 B) g" F7 m# ?
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
& W6 E9 z: d5 H<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>2 p5 u6 d% p; j0 ?* f3 Q. L7 x; b
<P>
1 ]6 O1 p: w3 e- Q3 s7 H因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：. F$ ]+ H  Z. c9 E# G2 P
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>
$ W5 i2 Y9 s& \/ M5 C8 j* S<P>取得存储过程的方法0 V+ Q% c& e3 {* F3 x# T/ P
存储系统对象的系统表/视图有：</P>
% g, [/ j+ ], X+ e<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>8 v2 \7 j" C. |
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>9 @. X6 o' R/ Q4 M6 Q
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS$ V( i( [; F+ \
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。
% S6 X; V& y( U8 {/ Y; LUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>/ R( y% ?4 J+ f1 k  h9 A
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>5 ]8 ]# m4 X5 q9 [* O* O. F) a
<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>+ M9 e, j; D: L! h
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>6 A6 o8 f; d7 q# S
<P>
5 b$ E2 L% U, V/ {# g对象类型有：</P># X4 {6 E: P, v
<P>CONSUMER GROUP</P>3 r3 v1 p) U4 G7 z8 H' {1 B
<P>CONTEXT</P>% D' G$ D. L0 F1 r0 ]1 j
<P>DIRECTORY</P>8 Q1 h" X1 M* ]
<P>FUNCTION</P>
. c9 w7 n. Y! `7 Z: V$ K<P>INDEX</P>) T# z/ g% {2 i% @5 f5 ?) P0 Y+ n
<P>INDEX PARTITION</P>" [4 Y" k& v; p2 g) p
<P>INDEXTYPE</P>) X  c" c$ H' k- Z* ?
<P>JAVA CLASS</P>
1 K. K' j- A; m<P>JAVA DATA</P>& g' b! U7 ?/ n' F: H5 }
<P>JAVA RESOURCE</P>
- M6 U  Z6 y  b3 {<P>JAVA SOURCE</P>, @/ Y: J  i  Q# t1 H* D
<P>LIBRARY</P>
- B- J+ K# c4 c2 D- T. d<P>LOB</P>
: T, `7 s$ I* s; @$ a5 P<P>MATERIALIZED VIEW</P>9 H9 N' W+ d% Z! W
<P>OPERATOR</P>+ J7 j5 O9 t) Y; d/ k
<P>PACKAGE</P>
0 f! G& e4 ?! A<P>PACKAGE BODY</P>
8 \4 B, H6 s: U4 K$ q6 ]# ^$ H  y* E<P>PROCEDURE</P>
: P, g) L! M+ q" s6 j0 i5 S<P>QUEUE</P>
( e* [7 Z" ?: l9 i- `" }/ F<P>SEQUENCE</P>
7 P( L' P8 O6 c5 W0 v<P>SYNONYM</P>9 B5 D4 B: A  A2 I/ L; {
<P>TABLE</P>( z0 l+ G! N. a" j3 B: m
<P>TABLE PARTITION</P>
! c; L, {. V& ~! t; D( ~<P>TRIGGER</P>; g& g+ d# J6 c9 H, ]2 O. a2 U& [  m
<P>TYPE</P>
$ h8 f5 t" r5 u; r* `: j* q<P>TYPE BODY</P>
: ?  Q% P* c/ E3 k, X9 T% N4 B<P>VIEW</P>! m; U& Q- F3 V( Q' Z5 a' g
<P>- Y" ]4 C3 W; E' r" r
因此，取得存储过程可以用：</P>- s) S4 d  K8 f7 Z; H8 T3 J
<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>
& G, @7 j2 e' C' M% _<P>取得隶属于某个用户的存储过程可以用：</P>
/ ]7 f: F& Y4 ]& F" H) 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 = 'PROCEDURE' AND OWNER = ‘DBAUDIT';</P>
9 P! s+ g7 P9 r3 g<P>  _2 h# W" s% D, D" o0 X% O
同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>7 O' [! a2 H1 L* L! k; b1 x8 ~9 ^
<P>取得存储过程内容的方法
& X$ D8 r- _; |4 c' S对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>
5 e1 }6 |6 s& ^# p* v7 ]<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
9 F, D  ], u/ @' F+ x<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>% n% c6 L- O7 g( ^
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE
/ C( Y; h# A- z2 h, t) D% |6 k5 v% ~; l存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。
# g7 L- N2 w* R4 {/ d8 S+ PUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
6 D4 D) h  a* S( p! B/ ?! p<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
; h+ N' m5 u  l+ g<P>
1 a8 d. B( c" @3 i+ J' [& T因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>
7 D) j- [# ]1 N5 C" ?4 _, K<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>) X' f9 y  r8 t" ?2 b5 g% U9 \
<P>取得函数的方法& ^* C! D' \. g* U! h
同上，取得函数可以用：</P>0 i4 @5 M& e, t; j' L' 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 = 'FUNCTION';</P>
# d% D- a' ~7 g8 d+ L" B<P>取得隶属于某个用户的函数可以用：</P>  ^5 b' S! B4 `- x& c6 s: E
<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>
, F0 h; r2 D, J! o) j7 |* t9 Y5 ?<P>
+ w( |5 ?( L7 k1 n取得函数内容可以用：</P>
; n( z8 l) c7 k1 W7 P9 E# a) S2 u<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>
. K; D9 h1 I+ H. d7 W<P>取得触发器的方法  \3 M& i) f* v
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
8 m1 O6 P' i3 S( ^<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
1 H, p! {7 m# J0 i8 b2 @描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。0 U: v8 g4 X0 Q
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS+ B$ I$ [& u& S' [" b! B% P0 _
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。3 g, J- A3 z$ ~( }/ U6 Q
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>3 p2 K9 F' S# e3 F0 V+ Q3 f( h
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>8 b9 `4 ]# a% H! _$ p
<P>
" i( n/ e% I$ L因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：
* U1 |/ `  R: x: E: M; RSELECT 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>- k, ]! R) ?1 B6 q+ K& j
<P>
# B  H. \. T$ {4 {取得触发器内容的方法：</P>) M: \" C3 h/ }
<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>
& O$ o2 d! V2 o. U' Z<P>取得索引的方法
4 s" M* N" a8 l& Y! w9 _* g( C7 N8 A存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>- [  b! L' u( {' |
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES
) t/ ~0 P- _1 q' p: u4 \9 p# w描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
- }# D- v  g6 {ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES
& N* f9 x! H* s5 t, e+ V# b+ M6 w; c- H描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。
- p/ o- G) C- \9 C) E" ]0 iUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>
+ A. y0 ], X- _<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>1 b# N2 K9 _; Z: [1 Z9 S4 v& [
<P>7 w% z# C) q" e# f
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：
* ~6 X" n2 ~: n7 T- r4 P+ NSELECT 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>
: d& G: B3 R# q6 h6 C1 C' J<P>5 M8 J8 e. `2 N1 J0 j7 L  X: }
取得索引相关的列的方法：</P>2 E! d7 c. W% f8 |. E& T
<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>
) }. a1 }2 k/ l: D<P>用户6 o/ ?& g6 }! U
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>
2 Q$ L/ h+ x. u! E" O<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
4 ^' j7 M" C; B9 n. y  ]描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
7 F  Z, [# v. m0 i& BALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
: K# S' W+ y" h5 h0 b: Y4 }描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
: d+ ]- Y7 i/ ]1 AUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>' z  w# j& ^( l! A$ h0 H+ H
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
6 i0 j& t8 }0 Q: Z& T<P>
( E4 c% u4 V* d! s  {* F3 \0 h因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
7 H/ \( ^& Q; |" F/ l) PSELECT 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