本文介绍了一种使用SQL Server的系统表和视图来查询表结构的方法，包括字段名称、类型、长度、是否允许为空等信息，并展示了如何判断字段是否为主键、聚类索引及自增长。
邹建写的
selectcasewhenc.colid=1theno.nameelse''end表名,--得到表名，重复的表名不显示
c.colid顺序,
c.name字段名,
t.name字段类型,
columnproperty(c.id,c.name,'PRECISION')字段长度,
isnull(c.Scale,'')小数位,
c.length占用字节,
casewhenc.isnullable=1then'√'else''end可为空,
casewhenc.colidin(selectik.colid
fromsysindexesi,Sysindexkeysik,sysobjectsoo
wherei.id=ik.idandi.indid=ik.indid
andi.name=oo.nameandoo.xtype='PK'--主键
ando.id=i.id
)then'√'else''end主键,
casewhenc.colidin(selectik.colid
fromsysindexesi,Sysindexkeysik
wherei.id=ik.idandi.indid=ik.indid
ando.id=i.idandi.indid=1--聚类索引
)then'√'else''end聚类索引,
casewhencolumnproperty(c.id,c.name,'IsIdentity')=1then'√'else''end自增长,
isnull(m.text,'')默认值
fromsysobjectso,syscolumnsc,systypest,syscommentsm
whereo.xtype='U'
ando.id=c.id
andc.xtype=t.xtype
andc.cdefault*=m.id
orderbyo.name,c.colid
AbateTime 1 ProductNo int 10 0 4 √ √
2 AbateDays int 10 0 4
4 NextDays int 10 0 4 √ (0)
5 ifauto int 10 0 4 √ (0)
AdvManage 1 No int 10 0 4 √
selectcasewhenc.colid=1theno.nameelse''end表名,--得到表名，重复的表名不显示c.colid顺序,c.name字段名,t.name字段类型,columnproperty(c.id,c.name,'PRECISION')字段长度,isnull(c.Scale,'')小数位,c.length占用字节,casewhenc.isnullable=1then'√'else''end可为空,casewhenc.colidin(selectik.colidfromsysindexesi,Sysindexkeysik,sysobjectsoowherei.id=ik.idandi.indid=ik.indidandi.name=oo.nameandoo.xtype='PK'--主键ando.id=i.id)then'√'else''end主键,casewhenc.colidin(selectik.colidfromsysindexesi,Sysindexkeysikwherei.id=ik.idandi.indid=ik.indidando.id=i.idandi.indid=1--聚类索引)then'√'else''end聚类索引,casewhencolumnproperty(c.id,c.name,'IsIdentity')=1then'√'else''end自增长,isnull(m.text,'')默认值fromsysobjectso,syscolumnsc,systypest,syscommentsmwhereo.xtype='U'ando.id=c.idandc.xtype=t.xtypeandc.cdefault*=m.idorderbyo.name,c.colid
AbateTime 1 ProductNo int 10 0 4 √ √
2 AbateDays int 10 0 4
4 NextDays int 10 0 4 √ (0)
5 ifauto int 10 0 4 √ (0)
AdvManage 1 No int 10 0 4 √
扫一扫
365
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
