数学建模社区-数学中国

标题: 获得数据库对象的方法探讨 [打印本页]
作者: 韩冰    时间: 2005-2-4 23:59
标题: 获得数据库对象的方法探讨
<><FONT color=#f70909>关键字 <a href="http://hackbase.com/hacker" target="_blank" >数据库</A> 对象# f" K& C; M5 l6 S7 F5 R' C* B3 y" H
原作者姓名 Fang</FONT> </P>
2 N! M7 J( D6 v7 n<>SQL SERVER
2 }5 x/ ?5 ~( j& ?" k- W取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
# n, r0 Q( u( F! y系统存储过程sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb</P>
5 S( Y2 B! T* [& E: C<>或者</P>0 e1 q( h" O+ g9 F. p# g2 {9 ~
<>USE master</P>% T" J, s8 b* q' F; B
<>SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid</P>. ^; k# `$ [6 F2 x" q2 u# L: _9 {
<>
8 ^" D5 _0 ?  I4 A1 X6 U+ Psp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。</P>! `* E) \: A' x  V: z( W1 R# Y
<>取得表的方法
. q8 {9 I+ s( l9 r2 a# s& }系统存储过程</P>
# T- j# i' r( ?" b  ?/ @- d<>USE xxx</P>
! J5 j& D& H! ^! {; b" K% m# {<>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>; B7 G  [) U- I" i5 Q( o5 e
<>或者</P>
8 ]/ }/ [" R/ _. e6 B; J<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的用户表：</P>
& ~# F6 c$ m! Z* y9 q+ g<>USE DBAudit
0 U4 s$ n. W7 N, W; _SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'</P>7 {" I- H6 U+ D- h0 U& ^+ ]
<>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的系统表：</P>" Z$ [$ e. Q) i/ o; a7 P
<>USE DBAudit
* h" J8 s& p4 i( K1 i& X7 u$ u2 XSELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'</P>
) {7 c& ?/ }% D<>或者统一使用：</P>
# B. l  x, i- J) N5 U<>USE DBAudit</P>
5 {$ ^+ H+ n. R5 Z7 U8 v  F<>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>
% n0 ?% a. \. W7 C: O<>注：</P>/ ~; N  ^: ~  B4 @
<>sysobjects中type字段类型解释：</P>0 v! Z; `+ M! H, C; S! d9 S
<>C = CHECK 约束</P>
6 N0 _+ m) V2 V$ G+ }) t<>D = 默认值或 DEFAULT 约束</P>( r( n5 g) H) B1 a
<>F = FOREIGN KEY 约束</P>
8 C9 b& ^& W( r) o# r; d( {<>L = 日志</P>
& n4 {9 R7 G: m5 [: e1 Y<>FN = 标量函数</P>
0 Y' ]7 b* B4 a2 D* T<>IF = 内嵌表函数</P>
9 ^8 J6 V/ {1 N0 F# P2 Z& x: e<> = 存储过程</P>
1 k6 P9 C7 d/ v* _+ I$ z! ~' K<>K = PRIMARY KEY 约束（类型是 K）</P>- Y0 z, M3 q; `* z
<>RF = 复制筛选存储过程</P>2 P( r5 T" e% g3 e0 W" D. ~
<P>S = 系统表</P>0 o$ b( q2 r* B# t# E# }! Q8 z* l
<P>TF = 表函数</P>
7 ~# V4 e. v- G$ u: h! A0 K<P>TR = 触发器</P>" o9 a& e/ t- p8 ^
<P>U = 用户表</P>
/ z1 F: {; K5 T<P>UQ = UNIQUE 约束（类型是 K）</P>
: K) a4 e+ B* g+ |; G<P>V = 视图</P>
% f4 `$ H# {: a- [<P>X = 扩展存储过程</P>, P* |1 J& W0 V8 i
<P>取得列的方法7 U. T- A5 V/ m
系统存储过程</P>
* Y. [: B. f5 ?$ N( s<P>USE xxxDatabase</P>
: }; _. z7 e$ b<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help xxxTable</P>1 E5 q2 t" ^* K/ A
<P>或者</P>  j8 c7 D7 U" z' l* G5 @* K
<P>USE xxxDatabase</P>
3 K0 `% \# C- `& Z$ F  X9 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>7 q( T" S; l* r6 P$ d
<P>这两种方法都可以取得包括视图的列。</P>
( F) z  f! o! J' m/ F<P>取得视图的方法
- c) r6 y% `: P; \' d系统存储过程</P>
  D$ H% j+ k% b<P>USE xxx</P>
: g2 w$ a) g9 s+ p$ T<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
6 l. }" i( h+ q) V; d<P>或者</P>& @! J: @6 B. {3 J
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图：</P>
* e' e! \+ t9 W2 z; `/ N* |" j<P>USE DBAudit</P>
+ X0 N( j/ r6 |* }- p7 c! i<P>SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V' </P>
# \1 W3 k: c0 b2 m% t8 p<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的视图的内容：</P>
7 S4 f5 W; P' m. `) }5 c+ \5 F<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>* v! J' P7 P5 [  |' _- d7 y$ s! _
<P>取得存储过程的方法2 {, v: S& J; P
系统存储过程</P>
2 i( F' z+ G  f; d+ @<P>USE xxx</P>8 m- ]# Q, s& q! s
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>
. Z" k8 V3 u  u, }# x# z/ S/ y<P>或者</P>, P6 ?' `8 e) G* i1 V1 e2 {
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程：</P>$ B" F# G. N# d. B3 g
<P>USE DBAudit</P>
% V: {) a; g. k" Z, I/ H8 ~6 }<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>4 a% s1 y) K+ `+ L/ B& G) V
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的存储过程或者扩展存储过程的内容：</P>
8 @6 F: F5 O5 W( y<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P># _+ x3 h0 y: S  ]
<P>无法获取加密的存储过程。扩展存储过程只能得到dll程序名。</P>" s7 Q0 V! ~; [1 _% `
<P>取得函数的方法& n9 D% O6 N  `+ s# O/ k5 g
系统存储过程</P>5 B6 y' X& I7 q' E. N* T
<P>USE xxx</P>3 H# [2 J, `- v4 K  g$ J
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>help</P>7 k& s; w$ ?3 t- K
<P>或者</P>& Z5 g& h4 @8 V$ I6 p
<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的各种函数（内嵌函数等）：</P>
# i/ V6 O7 c4 ?( v6 p7 V! K<P>USE DBAudit</P>( T& K) \! l  O* N" D# X; U1 I8 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>
/ G  C9 |% z0 U" v: ^  Q<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的函数的内容：</P>
1 M6 P. T8 v% N4 I5 r<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptext xxxObject</P>
/ S$ ]8 Q3 [3 U  o/ T  m4 R<P>只能获取用户定义的函数内容。</P>; C) [1 ^' F7 s# u/ B
<P>取得触发器的方法& l1 A, b7 X) u8 d! @" |/ I5 g+ Z# z
系统存储过程</P>' y( D% l' l2 e4 K: }5 ~
<P>USE xxx</P>
' C- s8 W6 {0 g5 n<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helptrigger xxxTable</P>3 n5 b4 ]% S# u" p. e
<P>或者</P>
/ G$ \  Z, `1 d9 a+ l! S<P>获取指定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>的表的触发器：</P>! z; _4 ~! E" |1 E5 c" V7 C
<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>( m, x/ H( c4 a/ L+ `! R
<P>取得索引的方法
. @4 F4 V+ x3 [0 D' U& D7 `系统存储过程</P>
3 D. k$ o9 T) \' h<P>USE xxx</P>+ ?/ \6 |" |0 D$ r% f3 s
<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpindex xxxTable </P>
: J$ Q, n2 M5 J, @, \8 d* J4 {<P>用户/ a9 n. x8 D8 S3 X" g
系统存储过程</P>
1 k7 ^9 Z( e6 r& o6 ?5 ^<P>USE xxx</P>
# q/ b- c  ^2 ]6 S5 u3 ]8 M. b<P>EXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helpuser</P>1 F2 m) s/ ~, g5 B4 `6 h* {$ z
<P>角色
! h7 m( A7 \3 R4 b系统存储过程</P>
6 q3 ?  Y: N& \4 [$ H$ U% b<P>USE xxx
' ?/ P0 Y0 a0 Q; C/ f5 G" UEXEC sp<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>helprole</P>" W) Z1 S: _" p- F2 _
. j5 L. q+ N' G8 d( w  C' [  a
<P>ORACLE
" ?$ Z% |7 X. h特殊
; E) f# W! F: Z&Oslash;         ORACLE只能连接特定<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>，不能自动扫描<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象。</P>
6 P: J: s' m: s8 A8 F<P>&Oslash;         ORACLE表必须用用户来区别，否则表可能重名。</P>) v; l- i' H1 d: D+ D
<P>&Oslash;         ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。</P>/ o7 E! o, P3 V  u; Q
<P>取得<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>名的方法
5 m0 O. g7 Q- W' v只能由用户指定<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>
  r, H0 A% Z, t9 H! A- a+ E<P>取得表的方法
3 g, q$ |3 p0 |" k7 t# k( G3 G存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的系统表/视图主要有：</P>
+ x9 r# y+ `4 a% |<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES</P>
  _1 E5 @2 N9 P* I& J% d<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象以及相关的表。</P>3 _3 p% a! j* Q9 d5 J* M
<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>
" Y1 `6 Z# `- J% o: H8 {<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的对象以及相关的表。</P>' u3 K( d$ D" ]5 |! _. H% f9 j
<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>0 M" S/ A! e: n8 S) z3 f% Q
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的对象以及相关的表。</P>* G7 }6 v% c: p; Z& T
<P>
5 F" i: R/ X2 o0 {DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>/ ]( Z7 W. L2 S& x& t0 z2 r& T
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有相关的表。</P>
; V/ t( X& m2 Q<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>1 h/ G& z) h, e9 j4 Y
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的相关的表。</P>
( G+ N0 w3 i' U2 i# B0 x<P>USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TABLES</P>
4 r/ O- V' m! d<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的相关的表。</P>; O4 `7 K1 m8 O8 r1 U& p# t
<P>5 L) A% ?* l" I: I* K  _# f
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TABLES中取得隶属于指定用户的表：</P>
+ r! a, {  x5 M( N7 \<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>
6 w5 X. j5 w* @" J<P>取得隶属于指定表空间的表：</P>8 B; c/ K5 r, T1 W
<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>4 V5 D6 W" c' n1 M
<P>
# K2 c6 w& J6 \5 P$ x也可以不指定用户名，从而取得所有的表。</P>6 F% r% M7 o+ Q# t! p% L' Y
<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>
, K1 w8 P- \. E4 @( z/ y$ h<P>取得列的方法6 k* h0 @- q2 K2 N# E5 v, k
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>表的列属性的系统表/视图主要有：</P>
: Z3 C7 t/ _# E$ p# g3 C/ a" M<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) Q  ~6 W* z6 ]* l描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的表的列属性。
6 ?, q8 h- k: X* B' }2 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
& X. q& r9 _- x/ k5 S描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。
9 {2 h, X& N( e% a. tUSER<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>COLUMNS3 R% A5 w0 B+ Q0 ^# z0 r. u, H0 \
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户拥有的表的列属性。
, K) P! \. j7 ~% I$ s7 V8 N2 yALL<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>
. W  a+ u( A( D6 t7 H<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的表的列属性。</P>
9 f, F. `- X( [( U. ]<P>
) s. V. J7 g# k! 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中取得隶属于指定用户的表：6 F0 w) M- G3 T3 C* 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>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>! D" Y9 N. y! ]* e  \4 B( m
<P>取得视图的方法
6 Y5 D' C: V( e( }; S3 j  A: m6 ^存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>视图的系统表/视图主要有：</P>
  t8 \3 Y% e! i* w" D! J<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
2 k$ U; X/ n* U描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的视图的属性。! G& W5 L! T/ d* E; j
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS
6 U8 Q  T/ E9 H* i5 b' D, W' N描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的视图的属性。
3 U& q& N+ @2 ]* @USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS</P>
2 N9 e8 W- v+ A5 c7 l: _+ |6 `. D<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有视图的属性。</P>& w+ z5 C* I; H6 ]: |* r
<P>% U' S; ?% ]9 X% E4 _
因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>VIEWS中取得隶属于指定用户的视图：6 H& ^# O8 I% `' l
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>" ], N. X" i7 T) J1 G
<P>取得存储过程的方法
; @. T6 E, H; t0 M4 e存储系统对象的系统表/视图有：</P>
6 c  I* I' W8 m9 ~2 _# p<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>9 h, B3 w+ I8 m' @. v  S
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的对象。</P>. [/ T4 v7 Y6 e! D6 H: p5 S
<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS
4 t0 X1 M/ R( t- x4 z$ c/ s描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的可以访问的对象。$ E/ k% v9 F, i7 ~( ~4 R4 V
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
; Z/ [" P# |( D- j<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有的对象。</P>; k2 y) X( V* S- R0 f( v! q
<P>SYS<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>OBJECTS</P>
' N9 D$ a) Y1 ?9 y/ ~<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的系统对象。？</P>
0 `/ a9 U9 e& o<P>
! R- ]2 S9 e& C$ Q$ q/ X+ I对象类型有：</P>
. o+ v( l( \  v  L+ L<P>CONSUMER GROUP</P>
- v6 I" y! v, l2 Z) I8 ?<P>CONTEXT</P>) l0 p7 ]/ {, I) f% y
<P>DIRECTORY</P>1 B) a5 |& q" X0 `4 V) F
<P>FUNCTION</P>
5 ^3 R- d* ?0 ]" m4 k) A- D( `<P>INDEX</P>0 z/ F/ T: [; G9 C  s$ ]
<P>INDEX PARTITION</P>
  R4 d! R" i3 l; k<P>INDEXTYPE</P>$ w/ Z+ g6 F  C2 t9 g" d! o
<P>JAVA CLASS</P>
( A! p; D  _0 X/ J3 P<P>JAVA DATA</P>
% Z8 O6 w7 i) Z$ t1 k( R<P>JAVA RESOURCE</P>
- i2 w5 O: z$ {* \7 k<P>JAVA SOURCE</P>" N; Q; e+ ~4 L6 e
<P>LIBRARY</P>- r8 b4 g1 x' ]6 U
<P>LOB</P>: v0 g/ S* M" G& q, _7 i; V: n
<P>MATERIALIZED VIEW</P>" E+ W; v- b6 e: O( r  V- f7 k
<P>OPERATOR</P>7 ~# t5 u3 Y% u! Y
<P>PACKAGE</P>7 q; q6 [% F# T1 U
<P>PACKAGE BODY</P>% \: \$ e4 j( T& d4 d  E/ J) R0 A
<P>PROCEDURE</P>
0 P6 Z% S# ~; U  r0 F4 N<P>QUEUE</P>
/ m; k% _" O5 \+ c<P>SEQUENCE</P>
: `3 o. M# \, l<P>SYNONYM</P>
0 u+ {# d% s. C3 g2 {8 z<P>TABLE</P>
# G% O3 m7 l' _/ A+ @" q<P>TABLE PARTITION</P>3 Q$ \; J! ^+ i* c* S4 _
<P>TRIGGER</P>" v) m3 m  y! e
<P>TYPE</P>
6 L! g$ {1 K6 I9 K<P>TYPE BODY</P>  U; [/ Z4 X. x: A! q* [; w5 E6 @
<P>VIEW</P>, D" q3 [4 g' D1 o/ p* e
<P>
4 @) a( @) Q7 C' Z. a$ n1 t因此，取得存储过程可以用：</P>% ^( j7 w" |( S) u! n3 q1 w" 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 = 'PROCEDURE';</P>. t( T, a- A: e' B$ u! z
<P>取得隶属于某个用户的存储过程可以用：</P>
2 a! b, y/ X% j# x7 |: E$ 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>! J( Z. t& e4 R- G
<P>
% s  i& A6 a, _$ z. d6 m6 |0 c) t同样，取得其他的对象也可以用这个方法，包括表，视图等。</P>' g7 V' |$ y! e
<P>取得存储过程内容的方法
$ A* m5 u3 @; m对象类型为类型、类型体、过程、函数、包、包体，JAVA源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的所有对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>都存储在几个系统表/视图中：</P>
. W+ i& z, \/ U) r8 Q. }& T- P- L<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>  \% q1 Z! ?6 [: @, H& R
<P>存储所有<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>
" h% x% q  x. L  G! }& K<P>ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE
" k: D! ^7 ~$ L: y9 B存储所有可以访问的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。/ e: E, Z& I; l4 O5 n, K
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>SOURCE</P>6 U! d3 r7 x& c5 d
<P>存储所有当前用户拥有的<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>对象的源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>。</P>/ N+ U% Q& E! t/ M8 F' a9 m
<P>; C" K/ n& \# \
因此，取得存储过程源<a href="http://hackbase.com/hacker" target="_blank" >代码</A>的方法：</P>) p+ ]  ^: X' A4 p2 {
<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>
/ I7 S5 `* Q6 ], V<P>取得函数的方法
- L& x% \+ g/ G. a+ j同上，取得函数可以用：</P># ?- D" j1 M" M) 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 = 'FUNCTION';</P>% J, X; E  z2 @" _7 D/ O: p4 \
<P>取得隶属于某个用户的函数可以用：</P>
9 ?( b% W- f$ [3 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' AND OWNER = 'DBAUDIT';</P>+ H# T. \% @5 x3 i+ }! ]" [( a3 N1 |
<P>, [2 r8 c1 r) M8 C- X; B
取得函数内容可以用：</P>- f- v0 i& g9 c% G
<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>
8 r3 {8 f- S6 @$ c1 R5 \, a8 y: E<P>取得触发器的方法
: r% ?) w* {5 Q# X2 T存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>触发器的系统表/视图主要有：</P>
3 u8 P7 w+ [8 O  P<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>TRIGGERS
( x- j# b1 L9 K, z8 u( j. J描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的触发器的属性。7 u$ V  l9 r6 K
ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS
, X/ G8 z! J2 E- N3 L描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的触发器的属性。8 Z4 K" v. h, g8 x4 h9 s* F/ G
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS</P>8 l0 `" N7 ?+ q
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有触发器的属性。</P># V! h. ~4 j7 l# x
<P>
( {9 X  z' i& n$ A8 d因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> TRIGGERS中取得隶属于指定用户的触发器：- {5 c$ ^2 c2 A# |
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>" `+ m' J; o* B
<P>
& F' \: ?. g/ ?  V' J) A取得触发器内容的方法：</P>. f# G" L% O! v$ A% `; ^# T
<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>
( T3 q- P% z+ m8 D  K. H. D% @  z<P>取得索引的方法
$ S/ u' N( R  e$ @# [5 S- N存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>索引的系统表/视图主要有：</P>% \$ }( H. J3 v: t7 g4 A
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>INDEXES
# N( G7 c  [4 j" o描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的索引的属性。
% u8 X4 b* \) u( S% jALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES. V  ^9 w$ k. R2 |# S" g/ g# G2 t
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户可以访问的索引的属性。% J1 O& I3 O' _
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES</P>' u9 k$ E: Z. D
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的当前用户拥有索引的属性。</P>( C6 f7 e" z$ P0 b7 y
<P>
( d. {7 j3 v3 @# U2 R( j因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A> INDEXES中取得隶属于指定用户的索引：
; B3 j) @- L+ N( @  ~; KSELECT 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>
- P( m& X/ m4 d+ W+ o+ V; ?: T<P>+ Q: A8 e1 x" P: k# Z
取得索引相关的列的方法：</P>
' }' S* _* ~  m$ P$ E% \: G<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>8 S- L. `8 l* {4 w2 v1 Q  u
<P>用户. E& `1 Z. w2 U: |3 s2 }" ^
存储<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>用户的系统表/视图主要有：</P>/ N9 v! V" [  L) c# h3 _
<P>DBA<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS" h$ N4 f# \" Z' W
描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。
& `6 b( Y& q9 z# F/ CALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS
+ g( Y& X* [8 b3 h描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中所有的用户的属性。" J: l$ W- }7 p  d) V! `
USER<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS</P>4 D. I- H* B/ ~1 `8 v7 [2 P
<P>描述<a href="http://hackbase.com/hacker" target="_blank" >数据库</A>中当前用户的属性。</P>
5 y- f( l+ M( f& a* A. R<P>
: A# J, D* j5 ?$ @! w6 g因此，可以从ALL<a href="http://hackbase.com/hacker/tutorial/200502019779.htm#" target="_blank" >_</A>USERS中取得用户：
$ l& H4 q! r  T+ E! M$ n9 O9 DSELECT 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