{"id":8349,"date":"2009-03-27T19:17:00","date_gmt":"2009-03-27T11:17:00","guid":{"rendered":"http:\/\/enet.yo2.cn\/articles\/8349,sql-server-%e6%9f%a5%e8%af%a2%e5%85%a8%e5%ba%93%e8%a1%a8%e4%bf%a1%e6%81%af-2.html"},"modified":"2009-03-27T19:17:00","modified_gmt":"2009-03-27T11:17:00","slug":"sql-server-%e6%9f%a5%e8%af%a2%e5%85%a8%e5%ba%93%e8%a1%a8%e4%bf%a1%e6%81%af-2","status":"publish","type":"post","link":"https:\/\/www.bohu.net\/blog\/8349\/","title":{"rendered":"Sql Server \u67e5\u8be2\u5168\u5e93\u8868\u4fe1\u606f"},"content":{"rendered":"

\u901a\u8fc7(sys.sysobjects)\u5bf9\u8c61\u8868\uff0c\u67e5\u8be2\u5168\u5e93\u8868\u7684\u57fa\u7840\u4fe1\u606f \uff1a<\/p>\n

SELECT name AS \u5bf9\u8c61\u540d, info AS \u5b57\u6bb5\u6570, \n      CASE WHEN xtype = 'C' THEN 'CHECK \u7ea6\u675f' WHEN xtype = 'D' THEN '\u9ed8\u8ba4\u503c\u7ea6\u675f' WHEN\n       xtype = 'F' THEN '\u5916\u952e\u7ea6\u675f' WHEN xtype = 'L' THEN '\u65e5\u5fd7' WHEN xtype = 'FN' THEN '\u6807\u91cf\u51fd\u6570'\n       WHEN xtype = 'IF' THEN '\u5185\u5d4c\u8868\u51fd\u6570' WHEN xtype = 'P' THEN '\u5b58\u50a8\u8fc7\u7a0b' WHEN xtype\n       = 'PK' THEN '\u4e3b\u952e\u7ea6\u675f\uff08\u7c7b\u578b\u662f K\uff09' WHEN xtype = 'RF' THEN '\u590d\u5236\u7b5b\u9009\u5b58\u50a8\u8fc7\u7a0b' WHEN\n       xtype = 'S' THEN '\u7cfb\u7edf\u8868' WHEN xtype = 'TF' THEN '\u8868\u51fd\u6570' WHEN xtype = 'TR' THEN\n       '\u89e6\u53d1\u5668' WHEN xtype = 'U' THEN '\u7528\u6237\u8868' WHEN xtype = 'UQ' THEN '\u552f\u4e00\u7ea6\u675f\uff08\u7c7b\u578b\u662f K\uff09'\n       WHEN xtype = 'V' THEN '\u89c6\u56fe' WHEN xtype = 'X' THEN '\u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b' END AS \u5bf9\u8c61\u7c7b\u578b,\n       CASE WHEN ftcatid = 0 THEN '\u5426' WHEN ftcatid = 1 THEN '\u662f' END AS \u7d22\u5f15, \n      crdate AS \u521b\u5efa\u65e5\u671f, CONVERT(char(19), getdate(), 120) AS \u672c\u6b21\u7d22\u5f15\u65f6\u95f4\nFROM sysobjects\nORDER BY name<\/pre>\n
======================<\/p>\n
\u8fd9\u91cc\u67e5\u8be2\u7684\u5305\u542b\u4e86\u7cfb\u7edf\u8868,\u5982\u679c\u8981\u8fc7\u6ee4\u6389\u7cfb\u7edf\u8868\u8bf7\u52a0\u4e0awhere\u6761\u4ef6!<\/p>\n
\u5982\u53ea\u8981\u7528\u6237\u8868\u548c\u7528\u6237\u89c6\u56fe: WHERE\u00a0\u00a0 (xtype = ‘U’) or (xtype = ‘V’)<\/p>\n
xtype \u53ef\u4ee5\u662f\u4ee5\u4e0b\u5bf9\u8c61\u7c7b\u578b\u4e4b\u4e00\uff1a<\/p>\n
AF = \u805a\u5408\u51fd\u6570 (CLR)<\/p>\n
C = CHECK \u7ea6\u675f<\/p>\n
D = \u9ed8\u8ba4\u503c\u6216 DEFAULT \u7ea6\u675f<\/p>\n
F = FOREIGN KEY \u7ea6\u675f<\/p>\n
L = \u65e5\u5fd7<\/p>\n
FN = \u6807\u91cf\u51fd\u6570<\/p>\n
FS = \u7a0b\u5e8f\u96c6 (CLR) \u6807\u91cf\u51fd\u6570<\/p>\n
FT = \u7a0b\u5e8f\u96c6 (CLR) \u8868\u503c\u51fd\u6570<\/p>\n
IF = \u5185\u8054\u8868\u51fd\u6570<\/p>\n
IT = \u5185\u90e8\u8868<\/p>\n
P = \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
PC = \u7a0b\u5e8f\u96c6 (CLR) \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
PK = PRIMARY KEY \u7ea6\u675f\uff08\u7c7b\u578b\u4e3a K\uff09<\/p>\n
RF = \u590d\u5236\u7b5b\u9009\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
S = \u7cfb\u7edf\u8868<\/p>\n
SN = \u540c\u4e49\u8bcd<\/p>\n
SQ = \u670d\u52a1\u961f\u5217<\/p>\n
TA = \u7a0b\u5e8f\u96c6 (CLR) DML \u89e6\u53d1\u5668<\/p>\n
TF = \u8868\u51fd\u6570<\/p>\n
TR = SQL DML \u89e6\u53d1\u5668<\/p>\n
TT = \u8868\u7c7b\u578b<\/p>\n
U = \u7528\u6237\u8868<\/p>\n
UQ = UNIQUE \u7ea6\u675f\uff08\u7c7b\u578b\u4e3a K\uff09<\/p>\n
V = \u89c6\u56fe<\/p>\n
X = \u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
 <\/p>\n
================================================================================<\/p>\n
\u91cd\u8981\u63d0\u793a\uff1a \u5c06\u6b64 SQL Server 2000 \u7cfb\u7edf\u8868\u4f5c\u4e3a\u4e00\u4e2a\u89c6\u56fe\u5305\u542b\u8fdb\u6765\u662f\u4e3a\u4e86\u4fdd\u6301\u5411\u540e\u517c\u5bb9\u6027\u3002\u5efa\u8bae\u60a8\u6539\u7528\u5f53\u524d\u7684 SQL Server \u7cfb\u7edf\u89c6\u56fe\u3002\u82e5\u8981\u67e5\u627e\u4e00\u4e2a\u6216\u591a\u4e2a\u7b49\u6548\u7cfb\u7edf\u89c6\u56fe\uff0c\u8bf7\u53c2\u9605\u5c06 SQL Server 2000 \u7cfb\u7edf\u8868\u6620\u5c04\u5230 SQL Server 2005 \u7cfb\u7edf\u89c6\u56fe\u3002\u540e\u7eed\u7248\u672c\u7684 Microsoft SQL Server \u5c06\u5220\u9664\u8be5\u529f\u80fd\u3002\u8bf7\u907f\u514d\u5728\u65b0\u7684\u5f00\u53d1\u5de5\u4f5c\u4e2d\u4f7f\u7528\u8be5\u529f\u80fd\uff0c\u5e76\u7740\u624b\u4fee\u6539\u5f53\u524d\u8fd8\u5728\u4f7f\u7528\u8be5\u529f\u80fd\u7684\u5e94\u7528\u7a0b\u5e8f\u3002<\/p>\n
\u5217\u540d \u6570\u636e\u7c7b\u578b \u8bf4\u660e<\/p>\n
name<\/p>\n
sysname<\/strong><\/p>\n
\u5bf9\u8c61\u540d<\/p>\n
id<\/p>\n
int<\/strong><\/p>\n
\u5bf9\u8c61\u6807\u8bc6\u53f7<\/p>\n
xtype<\/p>\n
char(2)<\/strong><\/p>\n
\u5bf9\u8c61\u7c7b\u578b\u3002\u53ef\u4ee5\u662f\u4ee5\u4e0b\u5bf9\u8c61\u7c7b\u578b\u4e4b\u4e00\uff1a<\/p>\n
AF = \u805a\u5408\u51fd\u6570 (CLR)<\/p>\n
C = CHECK \u7ea6\u675f<\/p>\n
D = \u9ed8\u8ba4\u503c\u6216 DEFAULT \u7ea6\u675f<\/p>\n
F = FOREIGN KEY \u7ea6\u675f<\/p>\n
L = \u65e5\u5fd7<\/p>\n
FN = \u6807\u91cf\u51fd\u6570<\/p>\n
FS = \u7a0b\u5e8f\u96c6 (CLR) \u6807\u91cf\u51fd\u6570<\/p>\n
FT = \u7a0b\u5e8f\u96c6 (CLR) \u8868\u503c\u51fd\u6570<\/p>\n
IF = \u5185\u8054\u8868\u51fd\u6570<\/p>\n
IT = \u5185\u90e8\u8868<\/p>\n
P = \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
PC = \u7a0b\u5e8f\u96c6 (CLR) \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
PK = PRIMARY KEY \u7ea6\u675f\uff08\u7c7b\u578b\u4e3a K\uff09<\/p>\n
RF = \u590d\u5236\u7b5b\u9009\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
S = \u7cfb\u7edf\u8868<\/p>\n
SN = \u540c\u4e49\u8bcd<\/p>\n
SQ = \u670d\u52a1\u961f\u5217<\/p>\n
TA = \u7a0b\u5e8f\u96c6 (CLR) DML \u89e6\u53d1\u5668<\/p>\n
TF = \u8868\u51fd\u6570<\/p>\n
TR = SQL DML \u89e6\u53d1\u5668<\/p>\n
TT = \u8868\u7c7b\u578b<\/p>\n
U = \u7528\u6237\u8868<\/p>\n
UQ = UNIQUE \u7ea6\u675f\uff08\u7c7b\u578b\u4e3a K\uff09<\/p>\n
V = \u89c6\u56fe<\/p>\n
X = \u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
uid<\/p>\n
smallint<\/strong><\/p>\n
\u5bf9\u8c61\u6240\u6709\u8005\u7684\u67b6\u6784 ID\u3002<\/p>\n
\u5bf9\u4e8e\u4ece\u65e7\u7248 SQL Server \u5347\u7ea7\u7684\u6570\u636e\u5e93\uff0c\u67b6\u6784 ID \u7b49\u4e8e\u6240\u6709\u8005\u7684\u7528\u6237 ID\u3002<\/p>\n
\"ms177596.note(zh-cn,SQL.105).gif\"\u91cd\u8981\u63d0\u793a\uff1a \u5982\u679c\u4f7f\u7528\u4ee5\u4e0b\u4efb\u4f55\u4e00\u4e2a SQL Server DDL \u8bed\u53e5\uff0c\u5219\u5fc5\u987b\u4f7f\u7528 sys.objects \u76ee\u5f55\u89c6\u56fe\u800c\u4e0d\u662f sys.sysobjects\u3002 CREATE | ALTER | DROP USER CREATE | ALTER | DROP ROLE CREATE | ALTER | DROP APPLICATION ROLE CREATE SCHEMA ALTER AUTHORIZATION ON OBJECT<\/p>\n
 <\/p>\n
\u5982\u679c\u7528\u6237\u6570\u548c\u89d2\u8272\u6570\u8d85\u8fc7 32,767\uff0c\u5219\u53d1\u751f\u6ea2\u51fa\u6216\u8fd4\u56de NULL\u3002<\/p>\n
\u6709\u5173\u8be6\u7ec6\u4fe1\u606f\uff0c\u8bf7\u53c2\u9605\u67e5\u8be2 SQL Server \u7cfb\u7edf\u76ee\u5f55\u3002<\/p>\n
info<\/p>\n
smallint<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
status<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
base_schema_ver<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
replinfo<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
parent_obj<\/p>\n
int<\/strong><\/p>\n
\u7236\u5bf9\u8c61\u7684\u5bf9\u8c61\u6807\u8bc6\u53f7\u3002\u4f8b\u5982\uff0c\u8868 ID\uff08\u5982\u679c\u7236\u5bf9\u8c61\u662f\u89e6\u53d1\u5668\u6216\u7ea6\u675f\uff09\u3002<\/p>\n
crdate<\/p>\n
datetime<\/strong><\/p>\n
\u5bf9\u8c61\u7684\u521b\u5efa\u65e5\u671f\u3002<\/p>\n
ftcatid<\/p>\n
smallint<\/strong><\/p>\n
\u6ce8\u518c\u4e3a\u4f7f\u7528\u5168\u6587\u7d22\u5f15\u7684\u6240\u6709\u7528\u6237\u8868\u7684\u5168\u6587\u76ee\u5f55\u6807\u8bc6\u7b26\uff0c\u5bf9\u4e8e\u6ca1\u6709\u6ce8\u518c\u7684\u6240\u6709\u7528\u6237\u8868\u5219\u4e3a 0\u3002<\/p>\n
schema_ver<\/p>\n
int<\/strong><\/p>\n
\u5728\u6bcf\u6b21\u66f4\u6539\u8868\u7684\u67b6\u6784\u65f6\u90fd\u4f1a\u589e\u52a0\u7684\u7248\u672c\u53f7\u3002\u59cb\u7ec8\u8fd4\u56de 0\u3002<\/p>\n
stats_schema_ver<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
type<\/p>\n
char(2)<\/strong><\/p>\n
\u5bf9\u8c61\u7c7b\u578b\u3002\u53ef\u4ee5\u662f\u4e0b\u5217\u503c\u4e4b\u4e00\uff1a<\/p>\n
AF = \u805a\u5408\u51fd\u6570 (CLR)<\/p>\n
C = CHECK \u7ea6\u675f<\/p>\n
D = \u9ed8\u8ba4\u503c\u6216 DEFAULT \u7ea6\u675f<\/p>\n
F = FOREIGN KEY \u7ea6\u675f<\/p>\n
FN = \u6807\u91cf\u51fd\u6570<\/p>\n
FS = \u7a0b\u5e8f\u96c6 (CLR) \u6807\u91cf\u51fd\u6570<\/p>\n
FT = \u7a0b\u5e8f\u96c6 (CLR) \u8868\u503c\u51fd\u6570 IF = \u5185\u8054\u8868\u51fd\u6570<\/p>\n
IT – \u5185\u90e8\u8868<\/p>\n
K = PRIMARY KEY \u6216 UNIQUE \u7ea6\u675f<\/p>\n
L = \u65e5\u5fd7<\/p>\n
P = \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
PC = \u7a0b\u5e8f\u96c6 (CLR) \u5b58\u50a8\u8fc7\u7a0b<\/p>\n
R = \u89c4\u5219<\/p>\n
RF = \u590d\u5236\u7b5b\u9009\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
S = \u7cfb\u7edf\u8868<\/p>\n
SN = \u540c\u4e49\u8bcd<\/p>\n
SQ = \u670d\u52a1\u961f\u5217<\/p>\n
TA = \u7a0b\u5e8f\u96c6 (CLR) DML \u89e6\u53d1\u5668<\/p>\n
TF = \u8868\u51fd\u6570<\/p>\n
TR = SQL DML \u89e6\u53d1\u5668<\/p>\n
TT = \u8868\u7c7b\u578b<\/p>\n
U = \u7528\u6237\u8868<\/p>\n
V = \u89c6\u56fe<\/p>\n
X = \u6269\u5c55\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
userstat<\/p>\n
smallint<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
sysstat<\/p>\n
smallint<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
indexdel<\/p>\n
smallint<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
refdate<\/p>\n
datetime<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
version<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
deltrig<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
instrig<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
updtrig<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
seltrig<\/p>\n
int<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n
category<\/p>\n
int<\/strong><\/p>\n
\u7528\u4e8e\u53d1\u5e03\u3001\u7ea6\u675f\u548c\u6807\u8bc6\u3002<\/p>\n
cache<\/p>\n
smallint<\/strong><\/p>\n
\u6807\u8bc6\u4e3a\u4ec5\u4f9b\u53c2\u8003\u3002\u4e0d\u63d0\u4f9b\u652f\u6301\u3002\u4e0d\u4fdd\u8bc1\u4ee5\u540e\u7684\u517c\u5bb9\u6027\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u901a\u8fc7(sys.sysobjects)\u5bf9\u8c61\u8868\uff0c\u67e5\u8be2\u5168\u5e93\u8868\u7684\u57fa\u7840\u4fe1\u606f \uff1a SELECT name AS \u5bf9\u8c61\u540d,  … \u7ee7\u7eed\u9605\u8bfb\u201cSql Server \u67e5\u8be2\u5168\u5e93\u8868\u4fe1\u606f\u201d<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,9,23],"tags":[144,271,298,336,359],"class_list":["post-8349","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server-sql","category-23","tag-end","tag-null","tag-pk","tag-select","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/8349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/comments?post=8349"}],"version-history":[{"count":0,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/8349\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/media?parent=8349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/categories?post=8349"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/tags?post=8349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}