|
SELECT
sysobjects.name AS
表名称
,ITPUB个人空间qpd6Bv c~~/0s%?7k(@
--sysproperties.[value] AS
表说明
,
Zqm6iAu:{6517
syscolumns.name AS
字段名称
,ITPUB个人空间'y:q,dLc,d2b
--properties.[value] AS
字段说明
,
ShKAM_p6517
systypes.name AS
字段类型
,
:x-q
z$[X'n6517
syscolumns.length AS
字段长度
,ITPUB个人空间3g.O
h/o}t"t:[
ISNULL(COLUMNPROPERTY(syscolumns.id,
syscolumns.name,'Scale'), 0) AS
小数位数
,
CASE WHEN syscolumns.isnullable=0
bhvB-?6517
THEN ''ITPUB个人空间ut"t.a;w;uO*g
ELSE '
√
'ITPUB个人空间)u(DZ)}�RM%M
END AS
是否为空
,
0JH2l#K
g6_3G6517
CASE WHEN syscomments.text IS NULLITPUB个人空间
S1s1~"?e/
G4}
THEN '' ELSE syscomments.text
*@,f9}4Hi
n[aNN6517
END AS
缺省值
,ITPUB个人空间
x;e){H^Y
CASE WHEN COLUMNPROPERTY(syscolumns.id,
syscolumns.name, 'IsIdentity')= 1
#U/v4i5]5Q"b6517
THEN '
√
' ELSE ''
Ds(~E7] D5}6517
END AS
递增字段
,
2xB
kOPva(m'I6517
CASE WHEN sysindexes.name IS NULL
5HH2Y!l6^U4f,o6517
THEN ''
;Pls)[|(PegV'~/�p6517
ELSE sysindexes.nameITPUB个人空间7d#]/6~?,UXh
END AS
索引名称
,ITPUB个人空间�hBP+l4uN#j
CASE WHEN sysindexkeys.keyno IS NULL
T|q2dNDeY*U+^*|0U6517
THEN ''ITPUB个人空间]X5FdH0V)K
ELSE
CONVERT(VARCHAR(10),sysindexkeys.keyno )
tk&]u
[P m6517
END AS
索引位置
,
~'l4qDj5~Z6517
CASE WHEN sysindexes.indid=1ITPUB个人空间s!v{]6u"Rw*S
THEN '
聚集索引
'
.kaIn1bCR�j6517
WHEN sysindexes.indid>1 AND sysindexes.indid<>255
KYU
S3_*Lu�X6517
THEN '
非聚集索引
'ITPUB个人空间,[L�zMd*O1C
WHEN sysindexes.indid IS NULL
R$s
b&?)O)Pt!zq%{5V6517
THEN ''ITPUB个人空间@
`%S2|.@n'Yg
ELSEITPUB个人空间q|"GZV]}o
'
其他
'
K%}7A+U{Kwb6517
END AS
索引类型
,ITPUB个人空间6z9k/ne8fM$i
CASE WHEN EXISTSITPUB个人空间-PS^;qs7^
(SELECT 1ITPUB个人空间Q
d!HC4oYW*q
FROM sysobjects
;P dp,e2SyBRO!H6517
WHERE xtype = 'PK' AND name INITPUB个人空间(p"~3ERM^~
(SELECT name
s(k#A{ ]z6517
FROM sysindexes
bl#o"ye)f"N/r6517
WHERE indid INITPUB个人空间2xsh9^!T
yRUE:[
(SELECT indidITPUB个人空间N;pQP-X+]2_5i
FROM sysindexkeys
!W]OR9z6517
WHERE id = syscolumns.id AND colid = syscolumns.colid)))ITPUB个人空间,/|#KPH:e#C,~
THEN '
√
' ELSE ''
5X#I4nOfL
]6517
END AS
主键
,ITPUB个人空间 UBE"d&p2J
CASE WHEN sysforeignkeys.constid IS NULLITPUB个人空间9R6m#S!]@ }y�b
THEN ''
:cM$P}
SS
X~`G0]
kH6517
ELSE '
√
'
C
X:Gyb1S(l6517
END AS
外健
FROM syscolumns --
数据表字段
INNER JOIN sysobjects --
数据对象
ON sysobjects.id = syscolumns.idITPUB个人空间?9g-| K&}x [3{
INNER JOIN systypes --
数据类型
ON syscolumns.xtype = systypes.xtypeITPUB个人空间;lT DDe[
LEFT OUTER JOIN sysproperties
properties --
字段属性信息
ON syscolumns.id = properties.idITPUB个人空间-B%b,Ft0`Q([+M
AND syscolumns.colid = properties.smallid
^!MM+Vy$P6517
LEFT OUTER JOIN sysproperties --
表属性信息
ON sysobjects.id = sysproperties.id
Ajf/y4{i'Q
H4@id6517
AND sysproperties.smallid = 0
TN4T$sc4E6517
LEFT OUTER JOIN syscomments --
注释信息
ON syscolumns.cdefault = syscomments.id
5T:i1YIm:y:@j�V~6517
LEFT OUTER JOIN sysindexkeys --
索引中的键或列的信息
ON sysindexkeys.id = syscolumns.idITPUB个人空间?-v;}$y;P
AND sysindexkeys.colid = syscolumns.colidITPUB个人空间k6Ae#Y;t|Z
LEFT OUTER JOIN sysindexes --
数据库
索引表
ON sysindexes.id = sysindexkeys.id
Ljnw"K[ sn6517
AND sysindexes.indid = sysindexkeys.indidITPUB个人空间+WD&gCA^
LEFT OUTER JOIN sysforeignkeysITPUB个人空间3B+a*e(`5}*}UV
ON sysforeignkeys.fkeyid = syscolumns.id
E HKA @(KB&L(x_6517
AND sysforeignkeys.fkey = syscolumns.colid
/!k/e%r8r6517
WHERE (sysobjects.xtype = 'U')ITPUB个人空间8JxTxl
order by sysobjects.id,syscolumns.colid
Z3x[ g!D,a"Ee~P6517
ITPUB个人空间chS`,O(?&a
8/n8U/@x.W;h6517
bd-jg WS ^2f�mD6517
ITPUB个人空间ozix3D%_L-s
ITPUB个人空间iJ1y0OU^[
ITPUB个人空间V}?�f6Ic�O
ITPUB个人空间Z5Q&TZ+DT'K
ITPUB个人空间z$]{QF/g
|
