数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]

作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象
3 u. }& q% ^5 I- T. C. U& G: q9 i5 T原作者姓名 Fang</FONT> </P>( N& T2 l! [7 l  H$ b4 E
<>SQL SERVER
, C1 H9 r1 l" K7 y3 ~& H& ]取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法# r& K, k9 m1 i  r: Z
系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>
" ]- i5 x& N1 D& i<>或者</P>% [' ~& m* d/ j- S7 Y$ X. w- t- ]
<>USE master</P>) |: O9 [; F  j$ J3 V0 J
<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>; N* R: k- _- h! J/ b
<>6 s1 L& k4 r0 c& J  i; j  m( M( ]
sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确:第二种方式无法获取Windows NT登陆用户。</P>
- E* R+ m2 W& ^, x) X% E3 S6 B<>取得表的方法3 o* x* }. [8 G0 B
系统存储过程</P>
: G' T9 y/ Z/ O6 u+ N  X3 `<>USE xxx</P>
2 |5 R8 c; d% n6 z1 J. s<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
) s+ u( V- r: b' F4 o, f4 A<>或者</P>! ^; t% M9 R# |" J
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表:</P>
; t) S& e5 L9 q0 o& w3 T1 Q( m<>USE DBAudit
- Y9 D1 @! S& ]& @, zSELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>9 j  G: T9 W1 Y& V0 s
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表:</P>
' Y  {$ d. ]- y7 m# J<>USE DBAudit
* o! [/ Y9 ?+ \/ z) a  K5 p- }SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
; c8 y4 o7 e: v9 ^) T! M<>或者统一使用:</P>& [3 u3 n7 \, d3 ?
<>USE DBAudit</P>
" ^4 x  g& h3 l1 K<>SELECT sysobjects.name AS name, sysusers.name AS owner, type, sysobjects.id AS id FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U' OR type = 'S'</P>+ b! ?9 Y# l& g+ e+ T
<>注:</P>8 v% l( c' g1 k3 M/ {
<>sysobjects中type字段类型解释:</P>
/ l- [$ H+ F. A9 q6 x! s; Y  e8 g/ d; p<>C = CHECK 约束</P>; F3 C1 V6 p# L- [2 |6 W# g
<>D = 默认值或 DEFAULT 约束</P>
# z( |& L% s# @7 l" {<>F = FOREIGN KEY 约束</P>2 o! B; l- M2 I5 r6 \) ~! \& p
<>L = 日志</P>) C$ z  X6 ~3 e1 ]3 ]: N8 X
<>FN = 标量函数</P>5 W# |. ]! ^) A2 f8 w3 U
<>IF = 内嵌表函数</P>
4 w! a: M- e6 D% H* h<> = 存储过程</P>/ m7 ^/ o4 R7 \" s
<>K = PRIMARY KEY 约束(类型是 K)</P>! @# O9 D5 t+ r2 r, I! P2 I
<>RF = 复制筛选存储过程</P>
# J5 P1 I# x- J5 I. S<P>S = 系统表</P>  p- D' m& M9 a1 p& Z0 ^8 V/ h  k
<P>TF = 表函数</P>! a: G4 k; _8 k7 L: b
<P>TR = 触发器</P>
1 y% I+ i, m1 Z( S# K0 k) ^<P>U = 用户表</P>+ Z1 ^3 i" I! A, i
<P>UQ = UNIQUE 约束(类型是 K)</P>
: s1 w/ P$ s9 |/ Z<P>V = 视图</P>
$ T4 f7 M( ]% J- O1 e5 Y" i<P>X = 扩展存储过程</P>8 h: b+ \( J: R/ ~
<P>取得列的方法
, W2 Y% |  s, R! T2 T7 F系统存储过程</P>
7 D" {& B( z- Y; A: Z<P>USE xxxDatabase</P>
9 ~- Y% c/ j, P* K: _<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P># g/ j1 Z# n+ w+ [9 D( E
<P>或者</P>/ M+ y8 s- k: ?0 h* B; J+ J0 a
<P>USE xxxDatabase</P>
1 J+ ^' b! m6 S% L" _0 ^. O<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>0 u* R& a$ W  w: W: C9 ^8 g
<P>这两种方法都可以取得包括视图的列。</P>& T4 Q/ A& O4 e. L/ w+ [
<P>取得视图的方法
5 S; g+ }+ ~; h* o系统存储过程</P>
' F  V- v' J. h8 e<P>USE xxx</P>
, t7 @: X. m; i! V' k$ S7 Y<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>/ k* K; J5 d! {3 m5 N  K! B+ G
<P>或者</P>0 s) V) [: l2 p! y  \: M3 c! A
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图:</P>' S* u/ I" L$ V+ V7 A
<P>USE DBAudit</P>
6 H+ u+ i( J! D, o9 ~5 }<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>4 |6 ~6 e" F: M6 l* |: I
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容:</P>5 b' z  }+ T+ b- x
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>  W. m1 b0 n+ L* v
<P>取得存储过程的方法, n, ~( B4 g5 j" A2 b
系统存储过程</P>+ }  b: f: k8 j7 ^& y1 t2 g: X; k
<P>USE xxx</P>/ `2 J1 [. q  ?& Q
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
& C! D6 s0 M( @. r4 ^<P>或者</P>
, ?  \5 f' S4 @8 [$ n* p<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程:</P>
1 |, ?% Y6 e7 F) Q( N, G$ x) a5 Y<P>USE DBAudit</P>+ I3 Q) }7 @7 K* @$ x/ D$ 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>0 c) B; g5 A1 u5 P! b+ {
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容:</P>
: d" M% V6 Y$ \5 X5 ]2 X0 w* r8 M<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
; j: A1 ^" {" }2 w7 K<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>
2 e9 o$ b+ ]( K" [! u+ ~3 ~/ ], j; H<P>取得函数的方法1 q: f/ D  ~$ r0 k& A/ E
系统存储过程</P># K% |0 m4 ^4 |% m% N1 a5 Y9 S
<P>USE xxx</P>
5 O/ P6 n' y! e" t* w' d<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
; ]  ~3 D  ~4 y<P>或者</P>
7 C1 T! Y8 @/ m<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数(内嵌函数等):</P>9 U; y  R% @) [" H( c# }
<P>USE DBAudit</P>
0 c& i; n6 k  p: @$ ?$ l# w- e<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>
5 _# Q/ l( q& {" B  F<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容:</P>
8 D8 {" g- ~& s4 Q6 n; Q: L<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>. R: m) c! g- M6 }" \; v6 d( ^
<P>只能获取用户定义的函数内容。</P>. O6 ~/ D6 t: x, K7 q4 m
<P>取得触发器的方法1 t/ v2 ]6 [6 c" k2 }' Z5 z
系统存储过程</P>
8 S) Z: a* d, m3 h0 U4 `6 a5 ]<P>USE xxx</P>6 ^3 M8 O( o. ^3 f1 @
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>+ P- J7 y4 ~3 a+ h7 o' T
<P>或者</P>, E0 c) m! i8 Q2 B# T
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器:</P>; j% M% C: w. m4 }" E1 |2 l
<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>, Z% [3 ]- m( l& |7 x- ~
<P>取得索引的方法* z6 y$ d8 i1 t6 A: e. r
系统存储过程</P>
! D" e' Q( {, ~: f$ k) u<P>USE xxx</P>
5 M+ u5 D; z0 d* q  m<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>0 v0 Y, d7 u7 Q; g- v  ~, R
<P>用户
! n) g( x2 j* t, b& Y" m. g. \# g系统存储过程</P>* V8 |0 m( E# b! d0 T3 N0 d5 D
<P>USE xxx</P>9 K  P) p  e  U5 T
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>
+ L$ }6 t" q/ }9 x% ]<P>角色( T; T, n! v- T/ W# K
系统存储过程</P>3 G; C% ?5 ^! Q0 W
<P>USE xxx, ~, a4 \) k0 z. F5 Z
EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>! O' }/ [& i  X! R; T% `

* d  P$ h0 N( ~5 Q8 ?( Q<P>ORACLE0 }# h8 K1 k  a6 Q, F3 R# D$ a
特殊3 f. r- ]/ d9 T* |7 s3 U6 u- ?2 _
&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>,不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>) \$ v3 Q0 B. x, T5 a" G- u: I
<P>&Oslash;         ORACLE表必须用用户来区别,否则表可能重名。</P>1 e' T+ o9 \; W7 H1 I( u( ^
<P>&Oslash;         ORACLE不能区别系统表或者是用户表,因此只能由管理员选择扫描某些特定用户的表。</P>
$ K  A6 j9 o" E3 E<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法9 A) [* a3 G! R4 B) W& 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>
. C+ I3 M* V0 o# B% u, F# }<P>取得表的方法
9 I0 s2 O& @" a/ d- ~2 ^" E存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有:</P>. J) n7 r7 @6 L& X8 _2 N2 q
<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>
8 f8 C# z% p2 _- j2 v<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P>
- t! E0 X" W/ K# i+ m8 B0 r: L, j<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>6 y5 Q: w6 {6 P' S4 E% o/ \; m
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>- C) }, f* v2 r$ \
<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>
( E" U0 `# s0 f2 b4 A<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>
3 [  E. o) y+ y$ \: R: q% C<P>
( V/ p  |, T' F# }4 Y& z) CDBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
+ f* o/ \. Q4 K<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>
5 }/ T) I2 [/ g9 e' p<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
. ?0 X3 A3 f# b<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>3 i5 u% e0 e5 O) K2 v3 j8 D* J
<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>  I7 ]* g: B5 g: t" K6 ^. j
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>
- W0 R& y) a5 }# }" |# V; K<P>9 L6 `8 D- X( }4 m$ p; C+ U( a7 c- P
因此,可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表:</P>
1 A2 y# f! M% g# [4 ^; k) N! 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 OWNER = 'DBAUDIT';</P>4 W3 ~" |7 n7 F6 ]3 @
<P>取得隶属于指定表空间的表:</P>
! e& J* m* V$ @* e3 Z: R3 y+ A<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>
: V1 a. O, {  B' x<P>
9 ]+ _# l. L9 ^" [9 Y! B: ~& L也可以不指定用户名,从而取得所有的表。</P>; ?  K" e+ d. d
<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>( R2 L+ T: j5 l8 P. D
<P>取得列的方法: m% S2 }. [# V4 }; E
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有:</P>
- A! u  G3 z, K<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
- K3 p. l* W+ z描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。
; w# H1 |* c- @2 q2 mALL<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- n5 L3 F- G& J5 l
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
7 L3 r. W/ z8 {2 r. DUSER<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( i. n; J4 K! C( X4 h9 q0 ?
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。2 |4 ^* p6 B3 I# @& H
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>6 z  r$ q0 c/ ]+ {9 [
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>3 p/ [) f) ?8 N  m
<P>6 C7 S9 ^1 I- ?1 I. y- _
因此,可以从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中取得隶属于指定用户的表:
: c4 x* S7 Z! P( E0 G# x( e. }. vSELECT 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>
6 K" Y+ @; d8 o5 Y+ I& c: M<P>取得视图的方法: a, ~+ h$ s3 `7 {# {0 b: X5 C
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有:</P>
* {1 x7 u$ K# u, D7 d5 y<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS- O  P! g( J- r- l, Y4 ]
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。9 U4 a- ?7 R( j0 h) T
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS! L2 m! m5 F  r# G; ]7 o# {
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。
- E* I: D: ?/ |6 h% B' ]USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
. _" R8 `& @: V3 G% W) h& K<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>
: [3 V# Y, n; E% r! a7 u  S<P>7 Q! J; c. Y, V; m  P1 S
因此,可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图:
# u& Y8 M( j# I5 K( S. USELECT 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>. B; n: B% e) r
<P>取得存储过程的方法
. e7 w4 k; S) d) t) Y- z存储系统对象的系统表/视图有:</P>
3 V4 c: g/ g9 e: j! C9 u* z! ^<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
" U+ d- O; |% J7 q- U<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>
6 t$ n* L$ A3 m" ?! r& S6 D9 M& j* _<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS% j% e  \& S3 p: G* B
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。( k& p9 T* M4 _$ q2 T/ g$ [
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P># B# C3 _% Z! J, E) [1 s9 Y
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>( e2 c0 R5 @. i  B
<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>. z5 j, I- F. V
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。?</P>
/ ]- _7 K: Y. }  z. b<P>1 s3 n. w* B6 p1 ~* W& E
对象类型有:</P>) @- r+ \/ b& H
<P>CONSUMER GROUP</P>( m* [7 l1 _9 Z2 z
<P>CONTEXT</P>
4 s9 K0 I: p* K* S2 ~+ H<P>DIRECTORY</P>6 d) R7 M4 o% u3 F4 Z! Y6 i
<P>FUNCTION</P>9 W, u4 T/ O( t
<P>INDEX</P>
6 D) d( a( P3 S5 G2 H<P>INDEX PARTITION</P>
; S8 b5 h* ?9 H2 G' K<P>INDEXTYPE</P># S2 E% i4 W# D4 r. ]+ e3 C+ N' S1 V
<P>JAVA CLASS</P>  F  c" x2 i  ~+ E0 @( ~
<P>JAVA DATA</P>
( o: @$ U% v. d/ p! J; a<P>JAVA RESOURCE</P>* T. L4 S  s# D1 K
<P>JAVA SOURCE</P>0 J+ _# I7 f- z1 F
<P>LIBRARY</P>' q9 t4 S7 k) |
<P>LOB</P>- Y8 f  o0 H) q
<P>MATERIALIZED VIEW</P>/ D8 K0 N7 ^1 n8 f
<P>OPERATOR</P># q% m+ k# V0 O# p4 G4 Q$ q$ ?- G! ~0 @
<P>PACKAGE</P>" S% K- |2 u" G. s
<P>PACKAGE BODY</P>
7 Y" b9 d  f* [6 q. P5 I# n( Z<P>PROCEDURE</P>
1 o5 R1 B, H$ w, N) `<P>QUEUE</P>
9 r' J  X3 k6 t) k% }& ~<P>SEQUENCE</P>$ v4 I7 K$ S  Z8 I& q; y& I0 J
<P>SYNONYM</P>
6 ~/ Q$ V. d7 V9 Z! H% {<P>TABLE</P>+ s! I3 W2 ~0 G& a- Q
<P>TABLE PARTITION</P>
0 a% u; b7 c$ l. q<P>TRIGGER</P>
) L, V1 I' l/ R8 S8 J1 ]# |9 }<P>TYPE</P>- }6 S9 p+ ~  G( G$ |
<P>TYPE BODY</P>
# k$ n4 G; X  _0 O, t0 p<P>VIEW</P>- m/ X, @( q! J! S, x
<P>3 `; B3 s. s4 ^( z% f4 J
因此,取得存储过程可以用:</P>
( l3 j/ N6 Y* p  Q<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! A- D7 _! t
<P>取得隶属于某个用户的存储过程可以用:</P>' C+ l6 ?2 B- N
<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>: F0 z  `5 G1 j8 H9 \
<P>0 w" s* m/ _0 A; X0 e
同样,取得其他的对象也可以用这个方法,包括表,视图等。</P>; J- G3 @( N4 D( Q$ c1 y
<P>取得存储过程内容的方法3 X' K% w* O3 H. k8 q
对象类型为类型、类型体、过程、函数、包、包体,JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中:</P>
7 x* Y/ T: `4 v& Y1 m% e* R9 m<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>$ V5 d4 W9 R& l- P, p; Y
<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>) Z8 E: |8 L) {+ @9 ]
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE
, T' k2 I2 f$ w存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。
1 s; o$ l% P8 w! s8 y0 d) C3 NUSER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>
7 w: \* L! E1 U2 H<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
8 g1 T" a/ s7 Y: v<P>
; {2 a$ |3 W. R& j) ]; q1 ?- Z1 _因此,取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法:</P>
5 A* J; @4 Y1 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>
1 Y/ z& m8 D+ ^+ u7 Y! |7 Y<P>取得函数的方法
2 e/ h+ X/ q0 s7 y; U同上,取得函数可以用:</P>
& F7 M) x* M! t4 f5 v<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>
8 x3 \, |- H( h) d. ^  P- M+ A( {<P>取得隶属于某个用户的函数可以用:</P>+ G6 n. j, }' D
<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 O- ?( _' m8 o# D2 x, G) |<P>2 i5 X: l+ p5 ?2 P4 J% ^6 w+ u! G
取得函数内容可以用:</P>1 A( x: z% ^$ U1 |0 I( [. y
<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>9 i" q7 Z( `- |8 `" B5 ]+ Q
<P>取得触发器的方法/ K  x' a& |. B' S7 m( P7 _/ S
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有:</P>
" }8 F! p8 o5 q( C7 \% m# s<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
2 l: V1 ]: @. `% M3 K描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。
/ M# c$ a* P* L; N! `8 @/ hALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
/ j' J+ x2 r' j4 t7 w描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。3 I! L9 r3 u5 E% ^- m- V, l
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>' Z- h" I3 ]- [, z! J7 @
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P>
/ ~; O2 f+ A' V  @# l<P>& W) ^( I' p- F8 C7 F; n
因此,可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器:
0 n7 H+ C; f. q5 e- @& T" uSELECT 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>
! |9 r; r/ ^2 b- o; z/ i<P>
! U6 x- t' [/ }9 u取得触发器内容的方法:</P>- X2 J2 F( L; @
<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 Q* Q+ {+ ?8 V5 e1 N/ S<P>取得索引的方法; Y. j! k. p, E8 A* D/ E
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有:</P>; x! K$ r( n# [* b" `$ b% R
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES% ?+ x2 r& Z7 r
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
9 G1 X0 w! `. Z; |) }& BALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES
/ j4 g& N: B( L% T1 z$ e' R描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。$ Z4 T& B' k) i2 C+ r- D% Q: P) G' P! d
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>3 }$ [" n4 b3 E" ~- h# h
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>" F, a8 f0 w2 G
<P>8 \1 C8 `$ M) R! z. g/ e; B  b
因此,可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引:6 s6 |$ c6 R% l5 G
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>  S4 G$ X5 i- z5 `2 j
<P>. u$ L3 f6 q# x; E. W
取得索引相关的列的方法:</P>8 Q6 B7 G% i( e" O4 D
<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- T% L6 C4 X9 E$ q
<P>用户% D1 m0 C, u1 R$ @5 k( N
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有:</P>
; l2 s5 o. |- C/ t6 G+ m4 j<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
$ x3 l- y; j+ j, _/ `4 ?# ]描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
  w; y* S# F- U# }1 kALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS% {/ T6 t$ W4 G2 i/ p, _' a1 y
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。" B: O2 Q; w/ ?. }: u# m6 F/ c
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>
% E" {, U  F4 \4 ~; L4 U8 H<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
$ J! W/ Z+ [$ {$ H2 @& l<P>
; p( h" E, @, S: F因此,可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户:8 ]$ ^1 {4 M3 _- X
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