获取SQLServer的最完整数据字典的SQL语句

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,'IsIdentity')= 1
#U/v4i5]5Q"b6517 ?THEN ' √ ' ELSE ''
Ds(~E7] D5}6517 END AS 递增字段 ,
2xB kOPva(m'I6517 CASE WHEN sysindexes.name IS NULL
5H H2Y!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|q2dND eY*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!H C4oYW*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 ''
:c M$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+V y$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 b d-jg WS ^2f?mD6517 ITPUB个人空间ozix3D%_L-s ITPUB个人空间iJ1y0OU^[ ITPUB个人空间V} ??f6Ic?O ITPUB个人空间 Z5Q&TZ+DT'K ITPUB个人空间z$]{QF/g

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!