查看完整版本: 列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

[size=3][/size]--======================================================

--列出[wiki]SQL[/wiki]SERVER所有表，字段名，主键，[wiki]类[/wiki]型，长度，小数位数等[wiki]信息[/wiki]

--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

--======================================================

SELECT
(casewhena.colorder=1thend.nameelse''end)表名,
a.colorder字段序号,
a.name字段名,
(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)标识,
(casewhen(SELECTcount(*)
FROMsys[wiki]object[/wiki]s
WHERE(namein
(SELECTname
FROMsysindexes
WHERE(id=a.id)AND(indidin
(SELECTindid
FROMsysindexkeys
WHERE(id=a.id)AND(colidin
(SELECTcolid
FROMsyscolumns
WHERE(id=a.id)AND(name=a.name)))))))AND
(x[wiki]type[/wiki]='PK'))%26gt;0then'√'else''end)主键,
b.name类型,
a.length占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION')as长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as小数位数,
(casewhena.isnullable=1then'√'else''end)允许空,
isnull(e.text,'')默认值,
isnull(g.[value],'')AS字段说明
FROMsyscolumnsaleftjoinsystypesb

ona.xtype=b.xusertype
innerjoinsysobjectsd
ona.id=d.idandd.xtype='U'andd.name%26lt;%26gt;'dtproperties'
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbya.id,a.colorder